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]

Yes Kush - expressed as a % change?

[Kushal_Chawda]

may be this
=FirstSortedValue(num(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)),AD_ExamName),'#0.00%'), 

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

[jlampard40]

Hi Kush - that doesn't seem to work.  I just get a '-' where the exam name should be.  Any ideas?

[Kushal_Chawda]

Do you mean you want to show exam name based on highest %?

[jlampard40]

Yes - % change, but to show highest change.  So like the - or + in front of Aggr statement, I would like to show the highest change and the lowest change.  Just by changing this symbol.  But I am lost with regards to the statement in the first place...!

[Kushal_Chawda]

try below.  Check which % change formula is correct.

// Based on Current/Prev12MonthsAvg
=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)),AD_ExamName))

or

// Based on (Current-Prev12MonthsAvg)/Prev12MonthsAvg
=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)))/
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)),AD_ExamName))

[jlampard40]

Hi Kush - I like the first one, works well.  But, it shows me the the highest or least positive change, using + or - in front of Aggr statement.  I have exams which have decreased and showing a decline - so how can I modify your code to show the highest declining exam in % terms??

[Kushal_Chawda]

Yes. Expression I used will show you based on highest % . If you want to show for lowest then you can simply remove '-' sign from second parameter of firstsortedvalue formula. 

[jlampard40]

Thanks Kush - it's showing the lowest positive change, but not the lowest change overall, which is -21%.  The change it's showing is +2% which IS the lowest POSITIVE change but not lowest change overall.  

[Kushal_Chawda]

We have used formula as Current/Prev12 Avg which will never give you negative difference as this is simply divide. Hence it will always return lowest positive % number. 

If you really want to calculate it based on % increase or % Decrease the you may need to change the formula

% Increase  - (Current - Prev12 Avg)/Prev12 Avg

% Decrease -  (Prev12 Avg-Current)/Prev12 Avg

Or let me know the logic of calculating % Increase & % Decrease so that we will be able to get desired output. But to be honest simple  % (Current/Prev12) is not gonna work here