Previous Quarter Calculation

[mike_landow]

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.

[Anil_Babu_Samineni]

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)

[mike_landow]

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

[sunny_talwar]

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)

[mike_landow]

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.

[sunny_talwar]

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

[mike_landow]

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.

[sunny_talwar]

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

[mike_landow]

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 )

[sunny_talwar]

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