topic Re: Current vs Previous Quarter - Capture Delta (+/-) in QlikView

Current vs Previous Quarter - Capture Delta (+/-)

ckmchinmaya — Sat, 16 Nov 2024 20:41:52 GMT

Hi Guys,

I have a requirement to show delta / net movement of values between Current Quarter ( Or any Selected Quarter ) with its previous quarter.

There is a possibility - we can't have quarters in sequence. Mostly we don't have Q4 datasets. So, if we need to compare between Q1'2019 then its previous quarter is Q3'2018. So, its purely based on user selection to select a current quarter.

I have values like 2017' Q4, 2018' Q1, 2018' Q2, 2018' Q3, 2019' Q1 & 2019' Q2. As I receive the value from a dataset as is, there is no date field / calendar to derive quarters.

Can I have any ideas how to proceed with this implementation?

Thanks a lot 🙂

Re: Current vs Previous Quarter - Capture Delta (+/-)

sunny_talwar — Mon, 10 Jun 2019 11:05:35 GMT

You can check this out Missing Manual Above and Below

Re: Current vs Previous Quarter - Capture Delta (+/-)

ckmchinmaya — Mon, 10 Jun 2019 12:29:09 GMT

Thanks Sunny for the link.

As I am looking to implement within script ( Quarter to Quarter delta to be shown in a text box object ), I have created a another column "display_year" which will convert 2018,Q1 to 20181 and similarly for other values.

year_quarter display_year

2019, Q1 20191

2018, Q3 20183

2018, Q2 20182

I have write below expression to get

=MaxString({1<display_year={'<$(=max(display_year))'}>} year_quarter)

This works perfect in text box object, but I am not able to write / make it work in a variable with in script.

Can you something you can help with ?

Re: Current vs Previous Quarter - Capture Delta (+/-)

sunny_talwar — Mon, 10 Jun 2019 12:32:54 GMT

You mentioned before that this needs to change based on user selection and yet you want to do this in the script? Also, you are using set analysis, whereas I suggested Above() and Below().... Did you try Above() and Below().

Re: Current vs Previous Quarter - Capture Delta (+/-)

ckmchinmaya — Mon, 10 Jun 2019 13:35:41 GMT

Hi Sunny,

Let me explain my scenario in more detailed way, We have 100+ KPIs which are calculated with in the script and displayed in presentation layer within text boxes - they are majorly part of executive summaries based on organization mandate. The Quarter selection is controlled in the presentation layer.

Now the requirement is to show a delta (net change) from last available quarter for each of the KPIs. So, I have to 1st recognise which one is the last quarter, then I can calculate its value to compute delta. Unless I do the whole calculation within the script - it will be a big unmanageable change in the presentation layer.

I am stuck at first point - to get last quarter while user select a current quarter.

The expression works as expected but, I am not able to use that with in script.

If you still think above/below will work - I can give a try.

Re: Current vs Previous Quarter - Capture Delta (+/-)

sunny_talwar — Mon, 10 Jun 2019 13:51:21 GMT

So, you don't want this to be done in a chart? You want this to be a KPI object? May be create a autonumber field like this

Calendar: LOAD Date, ..., Year_Quarter, AutoNumber(Year_Quarter) as YearQtrNum, .... Resident .... Order By Date;

and then you can use an expression like this

Sum({<YearQtrNum = {"$(=Max(YearQtrNum) - 1)"}>} Measure)

Re: Current vs Previous Quarter - Capture Delta (+/-)

ckmchinmaya — Mon, 10 Jun 2019 14:01:48 GMT

Can I use the expression it with in the backend script ?

Re: Current vs Previous Quarter - Capture Delta (+/-)

sunny_talwar — Mon, 10 Jun 2019 14:24:55 GMT

No, it is front end expression... I am not really sure what you are after... would you be able to provide info on the input data and the output you are hoping to see after you are done reloading the app?

Re: Current vs Previous Quarter - Capture Delta (+/-)

ckmchinmaya — Mon, 10 Jun 2019 14:42:31 GMT

Please find the attached screen, currently the KPI values are showing from 1 quarter ( based on user selection ). All these text box are written with variables. Now the ask is to show delta figure from previous available quarter with in bracket for all 4 organization. This is just 1 page out of 10. I want to do something with script ( help of variable ) and call the variable in presentation layer, so that it will easy if some more modification come in the later phase.

Re: Current vs Previous Quarter - Capture Delta (+/-)

sunny_talwar — Mon, 10 Jun 2019 16:01:39 GMT

So, all of these numbers are current quarter numbers? nothing is the prior quarter?

Re: Current vs Previous Quarter - Capture Delta (+/-)

qliksus — Tue, 11 Jun 2019 03:59:22 GMT

You can either create new column which has the previous quarter value for each quarter and use that to show the data or have a expression like the below

sum({<Quater={$(=max({<Quater={"<$(=only(Quater))"}>} Quater))}>} Amount)

Re: Current vs Previous Quarter - Capture Delta (+/-)

ckmchinmaya — Tue, 11 Jun 2019 09:04:32 GMT

Yes Sunny. All numbers are from current quarter ( based on user selection ).

Re: Current vs Previous Quarter - Capture Delta (+/-)

sunny_talwar — Tue, 11 Jun 2019 10:25:44 GMT

So, you did create a field like called display_year, right? How exactly did you create it? I mean what was the syntax you used in the script to create it?

Re: Current vs Previous Quarter - Capture Delta (+/-)

ckmchinmaya — Tue, 11 Jun 2019 12:12:16 GMT

Its simply a replace function removes Q and Comma from the year_quarter i.e. 2018, Q1. Post removal of Comma and Q it became a number 20181.

I have used the display year to calculate previous quarter, with the expression I shared earlier. Then I have put that quarter as a set expression to calculate KPIs, then compute Delta.

Though I have managed to get this done via multiple variables in the backend script and able to generate the required outcome. However, performance became a concern. At some point of time I have to change the way to get the previous quarter KPI values from a resident table only.

Re: Current vs Previous Quarter - Capture Delta (+/-)

sunny_talwar — Tue, 11 Jun 2019 14:12:02 GMT

The reason I say it might be easier to use

AutoNumber(Year_Quarter) as YearQtrNum,

compared to what you have is that it will be easy to go back one quarter when it is sequential number vs 201704, 201801....

Re: Current vs Previous Quarter - Capture Delta (+/-)

ckmchinmaya — Tue, 11 Jun 2019 14:21:54 GMT

Yes Sunny, the method you have suggested it will work for all type of scenarios. I found a lucky escape by simply replace few characters, but your suggested method will be the best.