Hi,
I have a specific metric to calculate. I need to calculate:
Average of the three last months of prior year - this is fixed: always October/November/December.
Second calc is the Average of three last months of current year. And this one is not fixed, it is moving to keep always last three monts.
I have seen a lot of proposals but thee are mainly calculations to get T|F flag. Is there any way I can have calc that returns AVG? The idea is that then I am gonna need to use that value for another calculations.
Thank you,
Pawel
@Pawel Lebek Find attached .twbx file for reference. I have created 4 calc fields like below:
1) This will give you prior year last month that is December.
PriorYearLastMonth:
DATE(DATETRUNC('month', DATETRUNC('year', {Max([Fiscal Month Year])})-1))
2) This will give you prior year going back 3 months from December so it will be Oct, Nov and Dec prior year
PriorYearLast3Month:
DATE(DATEADD('month', -2, DATETRUNC('month', [PriorYearLastMonthDate])))
3) This will give you prior year average:
PriorYearAvg:
{Fixed :Sum(IF [Fiscal Month Year] >= [PriorYearLast3Month] AND
[Fiscal Month Year]<= [PriorYearLastMonthDate] THEN [NoOfRecords] ELSE 0 END)
/3}
4) This will give you current year 3 months average:
CurrentYearAvg:
{Fixed :Sum(IF [Fiscal Month Year] >= DATEADD('month', -2, {max([Fiscal Month Year])}) THEN [NoOfRecords] ELSE 0 END)
/3}
Final output is where you want to show these calcs is upto you. See below:
Make sure you have those two filters added to context filters before your use them or if you want to use them in the calculation you need to add them.
Also I have created a field named NoOfRecords it just stamps the rows for counting. The field CNT(exam) is precalc as count in your datasource.
