topic Re: First Sorted Value based on average 12 month change in QlikView

First Sorted Value based on average 12 month change

jlampard40 — Sat, 16 Nov 2024 18:17:15 GMT

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

Re: First Sorted Value based on average 12 month change

Kushal_Chawda — Thu, 30 Jul 2020 13:09:43 GMT

Would you be able to share sample data?

Re: First Sorted Value based on average 12 month change

jlampard40 — Thu, 30 Jul 2020 13:21:17 GMT

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!

Re: First Sorted Value based on average 12 month change

Kushal_Chawda — Thu, 30 Jul 2020 13:45:45 GMT

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

Re: First Sorted Value based on average 12 month change

jlampard40 — Thu, 30 Jul 2020 13:49:34 GMT

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

Re: First Sorted Value based on average 12 month change

Kushal_Chawda — Thu, 30 Jul 2020 15:48:50 GMT

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

Re: First Sorted Value based on average 12 month change

jlampard40 — Thu, 30 Jul 2020 15:45:29 GMT

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

Re: First Sorted Value based on average 12 month change

Kushal_Chawda — Thu, 30 Jul 2020 15:49:46 GMT

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

Re: First Sorted Value based on average 12 month change

jlampard40 — Fri, 31 Jul 2020 07:33:41 GMT

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

Re: First Sorted Value based on average 12 month change

Kushal_Chawda — Fri, 31 Jul 2020 09:13:05 GMT

So % will be CurrentMonth/12Month avg?

Re: First Sorted Value based on average 12 month change

jlampard40 — Fri, 31 Jul 2020 09:23:58 GMT

Yes Kush - expressed as a % change?

Re: First Sorted Value based on average 12 month change

Kushal_Chawda — Fri, 31 Jul 2020 09:39:55 GMT

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

Re: First Sorted Value based on average 12 month change

jlampard40 — Fri, 31 Jul 2020 09:59:05 GMT

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

Re: First Sorted Value based on average 12 month change

Kushal_Chawda — Fri, 31 Jul 2020 10:32:29 GMT

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

Re: First Sorted Value based on average 12 month change

jlampard40 — Fri, 31 Jul 2020 10:34:28 GMT

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...!

Re: First Sorted Value based on average 12 month change

Kushal_Chawda — Fri, 31 Jul 2020 10:42:15 GMT

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

Re: First Sorted Value based on average 12 month change

jlampard40 — Fri, 31 Jul 2020 10:58:36 GMT

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

Re: First Sorted Value based on average 12 month change

Kushal_Chawda — Fri, 31 Jul 2020 11:05:59 GMT

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.

Re: First Sorted Value based on average 12 month change

jlampard40 — Fri, 31 Jul 2020 11:10:25 GMT

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.

Re: First Sorted Value based on average 12 month change

Kushal_Chawda — Fri, 31 Jul 2020 11:32:00 GMT

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