First Sorted Value based on average 12 month change

[jlampard40]

Hi - I want to produce a value which shows which medical examination has increased the most, in terms of activity, over the past 12 months.  So I need to calculate the average figure per month (over the past 12 months), then deduct it from the current month to get a positive or negative number.  We have thousands of exams and because of COVID we need to easily identify WHICH of the exams is changing the most as we are under pressure to cope now.

The dimension is called AD_ExamName

The data is sorted into months and the field is called AD_YearMonth

I then need to develop some set analysis which shows in a text box, which exam it is that has increased the most (AD_ExamName) from current month (AD_YearMonth='2020-07') against the past 12 months average...?

Thanks in advance for your help guys!

Rgds

[jlampard40]

Thanks Kush.  To be honest I'd given up trying to calculate it using set analysis and stuck all my data into a straight table.  I now have a column with the % change, so I can visually SEE on the table which is up, which is down etc in real and % terms.  I just wanted to put some sort of text box at the top of the dashboard which alerted users to the highs and the lows without them needing to look at the table in any great detail.

[Kushal_Chawda]

What is % change formula you are using?

[jlampard40]

I worked out the average per month over previous 12 months (not including this one).  I then deducted the total for June (2020-06) from the average per month over 12 months.  This gives me either a positive or negative number - which I then divide into the month of June (2020-06) to give me a %.  I've represented this on a straight table but if I could write set analysis to show this, and calculate this, it would be great.  I just gave up as am running out of time to do this and had to get something sorted - not ideal though.

If you can help, I'd be really grateful.  Thanks Kush!

Rgds

[Kushal_Chawda · Accepted solution]

I think this is what I had initially suggested as (Current -Prev12Avg)/Current

=FirstSortedValue(AD_ExamName, 
-aggr((count({<AD_YearMonth={"$(=date(max(AD_YearMonth),'YYYY-MM'))"}>}AD_Event_Key)-
avg(aggr(count({<AD_YearMonth={">=$(=date(addmonths(max(AD_YearMonth),-12),'YYYY-MM'))<=$(=date(max(AD_YearMonth),'YYYY-MM'))"}>}AD_Event_Key),AD_ExamName,AD_YearMonth)))/
count({<AD_YearMonth={"$(=date(max(AD_YearMonth),'YYYY-MM'))"}>}AD_Event_Key),AD_ExamName))