topic Prev Quarter Sales Calculation in Grid table in QlikView

Prev Quarter Sales Calculation in Grid table

bhargav_bhat — Wed, 29 Nov 2017 14:57:07 GMT

Hi All,

We are developing a grid table where we need to create two measures

1) Current Quarter Sales

2) Previous Quarter Sales

and the grid table will contain following columns

We are facing issues in calculating the Prev Quarter Sales measure

we have used below formulas to calculate the measure

sum({<QuarterID={"=$((QuarterID)-1)"}>}Sales)

We have also tried using variable approach

sum({<QuarterID={$(=$(vPQuarterID))}>}Sales)

but we are getting zero or null values

Needed some guidance on it

I have attached sample data file which contains sales at day level

Regards,

Bhargav

Re: Prev Quarter Sales Calculation in Grid table

Anil_Babu_Samineni — Wed, 29 Nov 2017 15:57:36 GMT

Can you define your variable in your second statement?

Re: Prev Quarter Sales Calculation in Grid table

sunny_talwar — Wed, 29 Nov 2017 16:07:15 GMT

May be you need this

Above(Sum(Sales))

Re: Prev Quarter Sales Calculation in Grid table

bhargav_bhat — Wed, 29 Nov 2017 16:13:55 GMT

Hi Sunny,

Thanks for replying

We tried using this function but we get null values for rows having previous row as different product sub type

Also the data will be sorted by total sales so the previous row might contain a different quarter

Regards,

Bhargav

Re: Prev Quarter Sales Calculation in Grid table

bhargav_bhat — Wed, 29 Nov 2017 16:15:01 GMT

QuarterID -1 is the expression in the variable

Re: Prev Quarter Sales Calculation in Grid table

sunny_talwar — Wed, 29 Nov 2017 16:15:30 GMT

May be this

Above(TOTAL Sum(Sales))

Re: Prev Quarter Sales Calculation in Grid table

bhargav_bhat — Wed, 29 Nov 2017 17:18:57 GMT

Hi Sunny,

I tried using Above(Total sum(sales)) but there two issues

All the dimensions needs to be sorted properly as above function takes previous row value
It takes wrong value from for rows where the previous product type or sub type is different as shown below

Re: Prev Quarter Sales Calculation in Grid table

sunny_talwar — Wed, 29 Nov 2017 17:23:49 GMT

Here are my questions

1) How many dimensions do you have in your chart? 2 or 3?

2) You don't want Product Type to cross over, right? Then you def. don't need TOTAL here... my first response stay... but I guess is that sorting can be off based on what you mentioned... in that case, you can give this a shot

Aggr(Above(Sum(Sales)), ProductType, YearQuarter)

Aggr function will sort first by ProductType and then by YearQuarter, no matter how the table is sorted. But the only issue that might come up is that if the sorting of YearQuarter field in the script is not done correctly, then you might see issues. These issues can be addressed if you are using QV12 or above or using Qlik Sense using The sortable Aggr function is finally here!

Aggr(Above(Sum(Sales)), ProductType, (YearQuarter, (TEXT)))

Re: Prev Quarter Sales Calculation in Grid table

bhargav_bhat — Wed, 29 Nov 2017 18:13:16 GMT

Hi Sunny,

Thanks a lot !!!!!

I used the following function for getting the Previous Quarter Sales

Aggr(Above(Sum([Total sales])),[Product Type],[Product Sub Type],(QuarterID,(NUMERIC, ASCENDING)))

Was not aware about the hidden features of AGGR function.It made the calculations a lot easier

Regards,

Bhargav