Average of user specific last 12 months period

[Neo1111]

Hello Guys,

I have a table like this

Period	Case_ID	Score	Category
202201	1	16	A
202201	1	6	B
202202	2	6	A
202203	3	6	B
202204	4	6	B
202205	5	6	C

There are 2 filter which are Period (always selected one) and Category(can be select multiple) .

so, when the user selected Period 202212 and Category A, I would like to show the average score for last 12 month of 202201-202212 and Category 1. (in this case is 11)

I user this to get 202212 and category A but cannot modify it to get last 12 months.

avg(aggr(sum(distinct Score),Case_ID))

I can use below set analysis to other expression without aggr.

{$<Period={"<=$(=Date(AddMonths(Max(Period),0), 'YYYYMM')) >=$(=Date(AddMonths(Max(Period),-11), 'YYYYMM'))"}>}

how to use it with aggr?

Thanks in advance.

[MayilVahanan · Accepted solution]

Hi

Try like below

avg({$<Period={"<=$(=Date(AddMonths(Max(Period),0), 'YYYYMM')) >=$(=Date(AddMonths(Max(Period),-11), 'YYYYMM'))"}>}aggr(sum({$<Period={"<=$(=Date(AddMonths(Max(Period),0), 'YYYYMM')) >=$(=Date(AddMonths(Max(Period),-11), 'YYYYMM'))"}>}distinct Score),Case_ID))

[Neo1111]

thanks for your quick response. it works.