Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
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
Highlighted
MVP
MVP

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

1 Reply
Highlighted
MVP
MVP

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