Hi,
I would like to find Last 3 years average. The problem is that there are nulls in between and I want to exclude them from the average calculation. I have a FIXED LOD for the products because they have historical data as well(for e.g. 2014, 2015 etc and they need to be present in the grand total). Can anyone explain how can I do this?
The calculated field has this formula right now:-
{ FIXED [Client]:
sum(IF [Year]=2020
OR [ Year]=2021
OR [Year]=2022
THEN [Revenue] END)} / 3
Good day Bhumika,
The problem is the fixed 3 for the denominator. You don't know for sure it will be a 3 since some years won't have any revenue for that year for that client. I'd suggest you calculate how many years each client has and use that as the denominator, maybe something like:
{ FIXED [Client]:
SUM(IF [Year] IN (2020, 2021, 2022) THEN [Revenue] END)
}
/
{ FIXED [Client]:
COUNTD(IF [Year] IN (2020, 2021, 2022) THEN [Year] END)
}
Does this help? Best regards,
-Dan
