(AVG AGGR SUM) for last month

[Neo1111]

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!

[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

[Neo1111]

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]

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]

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]

In that case, try like:

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