Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vishalmanu
Partner - Creator
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

 

vishalmanu_0-1596881792281.png

 

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

Quarter_YearSales
Q1  2010415880.26
Q1  2011272047.55
Q1  2012294067.11
Q1  2013536149.16
Q2  2010352786.14
Q2  2011337345.29
Q2  2012428279.06
Q2  2013518591.5
Q3  2010456684.8
Q3  2011546381.05
Q3  2012508179.27
Q3  2013722667.23
Q4  2010698981.68
Q4  2011788733.54
Q4  20121000205.74
Q4  20131074951.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_YearAvg(Sales)Cumulative Avg(Last 4 quarters) 
Q4  2013415880.26Q4 2013 + Q3 2013 + Q2 2013 + Q1 2013 
Q3  2013272047.55Q3 2013 + Q2 2013 + Q1 2013 + Q4 2012 
Q2  2013294067.11Q2 2013 + Q1 2013 + Q4 2012 + Q3 2012 
Q1  2013536149.16  
Q4  2012352786.14  
Q3  2012337345.29  
Q2  2012428279.06  
Q1  2012518591.5  
Q4  2011456684.8  
Q3  2011546381.05  
Q2  2011508179.27  
Q1  2011722667.23Q1 2011 + Q4 2010 + Q3 2010 + Q2 20102010 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

1 Solution

Accepted Solutions
Kushal_Chawda

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.

 

Screen Shot 2020-08-08 at 15.31.22.png

 

View solution in original post

2 Replies
Kushal_Chawda

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.

 

Screen Shot 2020-08-08 at 15.31.22.png

 

pradeep92
Partner - Creator II
Partner - Creator II

Cumulative.PNG

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