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: 
jlampard40
Contributor III
Contributor III

First Sorted Value based on average 12 month change

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

Labels (3)
2 Solutions

Accepted Solutions
Kushal_Chawda

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

 

View solution in original post

Kushal_Chawda

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

View solution in original post

23 Replies
Kushal_Chawda

Would you be able to share sample data?

jlampard40
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

Kushal_Chawda

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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?