Announcements
cancel
Showing results for
Did you mean:
Partner - Creator

## Calculate the running last four quarters

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

Labels (4)

• ### QLIKVIEW Script help

1 Solution

Accepted Solutions

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.

2 Replies

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.

Partner - Creator II

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

Community Browser