topic Previous Quarter Calculation in QlikView

Previous Quarter Calculation

mike_landow — Wed, 19 Apr 2017 19:47:26 GMT

Hi all,

I'm trying to resolve an issue that I think should be fairly simple, but I can't seem to get my head around it. Maybe I've just been staring at it too long. I need to generate a "Starting" value based on the data set below to generate the shown pivot table (obv a very simple sample set).

The "Start" amount should be, in essence, the "End" value of the previous quarter (sum(NewOrders)-sum(Invoices). However, in order to ensure the proper flexibility of selection, I am trying to perform a calculation to generate this data set, using the order and invoice dates and the proper Quarters associated. I think I just need to step away and maybe get a second set of eyes here, so any help would be greatly appreciated.

Start = sum(<Previous Qtr> Order Amount) - sum(<Previous Qtr> Invoice Amt)

New = sum(Order Amount)

Inv = sum(Invoice Amt)

End = (Start+New)-Inv

I can also attempt to script a summary data set, but if it can be done in the table expressions, that would provide me the most value.

Re: Previous Quarter Calculation

Anil_Babu_Samineni — Thu, 20 Apr 2017 03:02:14 GMT

You need to do like below

//Variable Creation

LET VPriorQuarter =Ceil(num(month(addmonths(max(DateFieldofQuarter),-3)))/3)

Then use these expressions

1) Start

sum({<DateFieldofQuarter = {'=$(=VPriorQuarter)'}>} [Order Amount]) - sum({<DateFieldofQuarter = {'=$(=VPriorQuarter)'}>} [Invoice Amt])

2) New

sum([Order Amount])

3) Inv

sum([Invoice Amt])

4) End

(Column(1) + Column(2)) - Column(3)

Re: Previous Quarter Calculation

mike_landow — Thu, 20 Apr 2017 12:27:07 GMT

This only works if I have a Quarter (or any date) selected. I need this to work with or without selections being made.

Re: Previous Quarter Calculation

sunny_talwar — Thu, 20 Apr 2017 12:31:33 GMT

May be like this:

Start = (Before(Sum({<Quarter>}Order Amount)) - Before(Sum({<Quarter>}Invoice Amt))) * Avg(1)

New = Sum(Order Amount)

Inv = Sum(Invoice Amt)

End = (Start+New)-Inv

I am assuming that you are going to try this in a pivot table where Quarter is pivoted. I have only ignore selection in just Quarter field, but ignore selection in all date and time related fields where you plan to make selections (Start expression)

Re: Previous Quarter Calculation

mike_landow — Thu, 20 Apr 2017 13:03:50 GMT

This is definitely on the right track. It is a Pivot table with the Quarter pivoted. It seems that this does get the previous Quarter data, but not the previous quarter "Remaining" as the current Quarter start point.

For example, in the data set provided, Q1-2017 actually ends up with a Start value of -400 (instead of 500) due to the amount of New and Invoices in Q4-2016, but not carrying over the Starting value.

Re: Previous Quarter Calculation

sunny_talwar — Thu, 20 Apr 2017 13:06:14 GMT

Would you be able to share a sample to look at this... because as far as I understand, this should get the remaining value....

Re: Previous Quarter Calculation

mike_landow — Thu, 20 Apr 2017 13:51:02 GMT

I mocked up a bit of data that is based on the live data. Just a small sample of Orders and Invoices. This is basically the set structure I'm working with. I attached it to the main Post of this discussion - since doesn't seem I can attach it to the reply.

Right now a Pivot Table in QlikView or Qlik Sense is the goal, but will potentially want a waterfall design view down the line.

I think part of the issue is "gaps" in the date sequence as well, so there may be a need to fill the gaps of data appropriately, and I'm also looking at that.

Re: Previous Quarter Calculation

sunny_talwar — Thu, 20 Apr 2017 13:57:38 GMT

How have you created your calendar here? Have you used Canonical Date approach or another approach? The reason I ask is that it seems that you need have two types of dates and you will need to create Quarter field from the combination of two... just wonder how you have done it.

Also, check here to see how you can attach to a response

Uploading a Sample

Re: Previous Quarter Calculation

mike_landow — Thu, 20 Apr 2017 14:21:50 GMT

Ah! Thanks for the tip of the "Advanced Editor". I saw that, but missed the attach icon at the bottom.

As far as the calendar goes, I have used a similar method to the Canonical Date approach, although I do not have a separate calendar for each date. There is a company master calendar data set that I am loading in. This set has all the information related to a date in regards to QTR, Month, Year, Fiscal Month, etc.

I've created a Link table that allows for the use of a singular date selection, but ability to use Order Date and Invoice Date together, as necessary. This is linked to the Orders and Invoices based on a key created using the order number/line of business/(order or invoice) date. (Whether it is working correctly...that's another story )

Re: Previous Quarter Calculation

sunny_talwar — Thu, 20 Apr 2017 14:28:59 GMT

Just to be consistent, would you be able to share the script you used to create the link table and calendar?

Re: Previous Quarter Calculation

mike_landow — Thu, 20 Apr 2017 16:37:38 GMT

Sample code attached. Note that the Calendar has standard calendar fields and fiscal (ENT_ prefix).

I am not certain my code is correct either, so I am more than open to suggestions.

As some additional thought processes, in reality, the calculation I need is the aggregation summation of (new orders)-(invoices) over the time period prior to the dimension of the table. So, for say, Q1-2017, I would want the sum of all new orders - total invoices for all dates prior to the start of Q1-2017, which would give me my "Start" value.

Re: Previous Quarter Calculation

sunny_talwar — Thu, 20 Apr 2017 17:01:13 GMT

Does that look right to you or not?

Re: Previous Quarter Calculation

mike_landow — Thu, 20 Apr 2017 17:05:20 GMT

I think that looks pretty good. I think you hit on what I was missing, which is filling in the "gaps" in the date sequences to get the sequential quarters to work properly.

Let me work with this for a bit and I'll get back on here either way.

Thank you for all your help Sunny!

Re: Previous Quarter Calculation

mike_landow — Thu, 20 Apr 2017 22:50:06 GMT

Hi Sunny. I think that this is very close, and I'm going to play around with it a little, but it appears to break down if we change the sort of the chart. The chart needs to be sorted by Quarters asc, so Q4-2015, Q1-2016, Q2-2016, etc.

The chart you built works great, but the sorting is incorrect - Q1-2017, Q2-2016, Q3-2016.

If I change the sort order to align properly, the chart changes its presentation to this:

If you look at Q2-2016, it sets the Start value to 0, because this is only looking at the previous quarter New/Inv data, but in reality, it needs to aggregate all the New/Inv numbers for all previous quarters. That is another approach I am looking into. To not necessarily focus on a quarter-by-quarter calculation, but just aggregating previous quarterly data over time.

Re: Previous Quarter Calculation

sunny_talwar — Fri, 21 Apr 2017 01:40:34 GMT

If you change the sorting and you still want the same result, then you can use After instead of Before

Re: Previous Quarter Calculation

mike_landow — Fri, 21 Apr 2017 12:56:08 GMT

Still not quite right. If you add the dimensions for LOB and Sales Order, the numbers don't add up. I think this has to include some combination of the before/after expression, but also a RangeSum.

In essence, this is a rolling sum of the New-Inv amounts over time, presented within each Quarter.

Re: Previous Quarter Calculation

sunny_talwar — Fri, 21 Apr 2017 12:58:23 GMT

What is the output you expect to see? May be if I know the numbers I am able to get, I might be able to help better

Re: Previous Quarter Calculation

mike_landow — Fri, 21 Apr 2017 13:34:49 GMT

Hi Sunny. I probably should have created a better sample screenshot at the initial post

Based on the mocked up data, this is roughly what I am trying to get created:

As you'll notice, the Start value for each quarter is, basically, the running total of (New-Inv) over the previous timeframes, and the End value is the addition of that Quarters (New-Inv). So, it really is more of a running sum, than necessarily a Previous Quarter calculation. And that is my mistake as to how I presented it in the initial thread.

The 0 values don't need to be displayed as such. Nulls() are OK, as long as the overall numbers sum properly.

Re: Previous Quarter Calculation

sunny_talwar — Fri, 21 Apr 2017 13:55:28 GMT

Here (besides some potential typos in your attachement above the expression seems to work) this seems to be working

Start

RangeSum(Before(Sum({<QuarterYear>}[Order Amount]), 1, ColumnNo())) - RangeSum(Before(Sum({<QuarterYear>}[Total Global Rev Amt]), 1, ColumnNo())) * Avg(TOTAL 1)

Re: Previous Quarter Calculation

mike_landow — Fri, 21 Apr 2017 14:46:42 GMT

Thank you Sunny! (Guess I should have finished my morning coffee before trying to do math in my head to make that chart ).

This looks like it's working exactly as I need it. Or, at least it gets me over the hurdle to move forward. Thank you for all your help. I really do appreciate it.

You're my hero for sure today!