Calculate the running last four quarters

[vishalmanu]

Hello Everyone,

I have a scenario, where i need to calculate the accumulation of last four quarters in a straight table in qlik.

Below is the scrip that i have used to get the Quarter_Year field based on a simple OrderDate field

I have the output as below in the straight table and i have just used the Avg(Sales) as expression.

Quarter_Year	Sales
Q1  2010	415880.26
Q1  2011	272047.55
Q1  2012	294067.11
Q1  2013	536149.16
Q2  2010	352786.14
Q2  2011	337345.29
Q2  2012	428279.06
Q2  2013	518591.5
Q3  2010	456684.8
Q3  2011	546381.05
Q3  2012	508179.27
Q3  2013	722667.23
Q4  2010	698981.68
Q4  2011	788733.54
Q4  2012	1000205.74
Q4  2013	1074951.94

and i need to create another column which shows the running last four quarters as accumulation,  I used the Rengesum(Below(avg(sales),0,4)) and it worked, but the output is not the one as i expected. In the above table i need to exclude the year 2010, but i f exclude it then i will not be able to calculate the accumulation of Avg for Q1 2011 (Avg Q12011+ avg 4 2010+ avg Q3 2010 + avg Q2 2010).. And morever i am not able to sort the quarter field in an order.. I would like to see the output as below.

Quarter_Year	Avg(Sales)	Cumulative Avg(Last 4 quarters)
Q4  2013	415880.26	Q4 2013 + Q3 2013 + Q2 2013 + Q1 2013
Q3  2013	272047.55	Q3 2013 + Q2 2013 + Q1 2013 + Q4 2012
Q2  2013	294067.11	Q2 2013 + Q1 2013 + Q4 2012 + Q3 2012
Q1  2013	536149.16
Q4  2012	352786.14
Q3  2012	337345.29
Q2  2012	428279.06
Q1  2012	518591.5
Q4  2011	456684.8
Q3  2011	546381.05
Q2  2011	508179.27
Q1  2011	722667.23	Q1 2011 + Q4 2010 + Q3 2010 + Q2 2010	2010 should not be shown in the chart but the cumulative should be added to Q1 2011

Kindly help me in achieving this task. attached is the sample excel file.

Kind Regards,

vishal

[Kushal_Chawda · Accepted solution]

try below

Create Quarter_Year field as below

 dual('Q'&ceil(Month(OrderDate)/3)&'-'&year(OrderDate), QuarterStart(OrderDate))

Then you can use below expression

sum({<Year={">2010"}>}aggr(RangeSum(Below(total Sum({<Quarter_Year>}Sales),0,4)),(Quarter_Year,(NUMERIC, DESCENDING))))*avg(1)

Go to presentation tab and check suppress zero values option.

[pradeep92]

Hello Kushal,

I have a similar requirement where I need to accumulate the data for each financial year with its quarters.

eg: for FY2022Q1 , it should start with 94,229 again in rangesum and sum up its Q2,Q3 and Q4 quarters accordingly.

Expression that I used,

RangeSum(Above(Sum({<PI_Quarter_Flag ={1},Fiscal_Year_Qtr>}ACTUAL_HARD_SAVINGS_CUSTOMER/1000), 0, rowno()))