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

[Kushal_Chawda]

Would you be able to share sample data?

[jlampard40]

This is a snapshot of the data we are using.  It's run daily, but we have 5 years worth of it (all in Excel spreadsheets).  We 'count' on AD_Event_Key as this is a unique value.  The AD_ExamName refers to the type of exam and you can see the various date fields we have towards the end of the columns.  As we are trying to compare an average of past 12 months against the current month of 2020-07 we'd need to work this out.

I would then like to show, in a text box, the VALUE of the AD_ExamName which is the largest difference (positive difference) from this month to the past average of 12 months gone.

Many thanks!

[Kushal_Chawda]

Calculation will always be for current month or  it is dynamic? Like if you select any month then from that month to previous 12

[jlampard40]

Yes.  Calculation will always be for current month (2020-06) against the previous 12 months average.  

[Kushal_Chawda · Accepted solution]

try below

First convert  AD_YearMonth to proper Month Year format

Data:
LOAD AD_Event_Key, 
     AD_ExamName, 
     AD_Modality, 
     AD_Urgency, 
     AD_FY, 
     AD_Q, 
     AD_CalYr, 
     date(date#(AD_YearMonth,'YYYY-MM'),'YYYY-MM') as AD_YearMonth,
     AD_FYWk
FROM
[QV Test 300720.xlsx]
(ooxml, embedded labels, table is Sheet1);

Now you can use below expression in text object. Here my consideration is if you are in Jul-2020, previous 12 months will Jul-2019 to Jun-2020. i.e  excluding current month from previous 12 months

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

[jlampard40]

Brilliant! Thanks so much Kush.  Can I just ask, can I do the same, but in % terms for sorted values??

[Kushal_Chawda]

Yes. You can use it. How % will be calculated?

[jlampard40]

Hi Kush - using your methods above, I can now show which exams are showing the most and least change from previous month, compared to 12 month average.  However, I'd like to put this into perspective by also showing the % change, from previous month to past 12 months.  So, same methodology as your solution really, just showing it in % terms too.

Many thanks in advance Kush!

Rgds

[Kushal_Chawda]

So % will be CurrentMonth/12Month avg?