Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Neo1111
Contributor II
Contributor II

Average of user specific last 12 months period

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.

Labels (3)
1 Solution

Accepted Solutions
MayilVahanan

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))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

2 Replies
MayilVahanan

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))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Neo1111
Contributor II
Contributor II
Author

thanks for your quick response. it works.