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

[ckmchinmaya]

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 🙂 

[sunny_talwar]

You can check this out Missing Manual Above and Below

[ckmchinmaya]

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 ?

[sunny_talwar]

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

[ckmchinmaya]

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.

[sunny_talwar]

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)

[ckmchinmaya]

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

[sunny_talwar]

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?

[ckmchinmaya]

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.  

[sunny_talwar]

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