Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Neo1111
Contributor II
Contributor II

(AVG AGGR SUM) for last month

Hello Every one, I am new to here. Nice to meet you all. 

Currently, I am working on a dashboard with the data model something like this.

Case_no|Score|Period|Category

001|1|202212|Cat1

002|2|202301|Cat1

I use =Avg(aggr(sum(Distinct Score), Case_no,Category))

to get the average score of the user selected Period.

What should I do to get the previous month avg score? eg. user selection = 202301 and display 202212 avg score .

Thanks in advance!

Labels (1)
5 Replies
vikasmahajan

Hi,

Create MonthYear Field in your calendar  & try following expression :

Avg(aggr(sum(MonthYear= {"$(=max(MonthYear)-1)"}>} Distinct Score), Case_no,Category))

Regards

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Neo1111
Contributor II
Contributor II
Author

I have already created a field called Period. it is YYYYMM.

But it doesn't work when I replace period to monthyear.

Is calendar equal to autocalendar? are they the same?

tresesco
MVP
MVP

It depends on how you created the period field in the script. If you have used monthname() or similar (not text function or concat), osomething like below should work:

=Avg({<Period={"$(=Date(AddMonths(Max(Period),-1), 'YYYYMM'))"}>}Score)

and if you don't want to change anything in the script, you can try like:

=Avg({<Period={"$(=Date(AddMonths(Date#(Max(Period),'YYYYMM'),-1), 'YYYYMM'))"}>}Score)

Neo1111
Contributor II
Contributor II
Author

Oh, my data was imported from Alteryx.

When I imported the data, I have already set it input format as YYYYMM.

For testing, If I put Month(Period), it returns the correct month like Dec or Nov.

I tried your expression, it works, however, the score has to be distinct to Case_no and Category.

I guess it is time to use aggr. but it just doesn't work.

tresesco
MVP
MVP

In that case, try like:

Avg ( {<working set>} Aggr ( {<working set>} Sum(....