Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasmahajan

Prior Quarters & month growth% calculation in pivot table

Hi All,

Please find attached test data for each month sales my requirement is to calculate a  growth for every quarter for all periods. 

like this :

Sales_GRW
Q3 2016
Sales_GRW
Q4 2016
Sales_GRW
Q1 2017
Sales_GRW
Q2 2017
Sales_GRW
Q3 2017
Sales_GRW
Q4 2017
Sales_GRW
Q1 2018
Sales_GRW
Q2 2018
Sales_GRW
Q3 2018
Sales_GRW
Q4 2018
Sales_GRW
Q1 2019
Sales_GRW
Q2 2019
Sales_GRW
Q3 2019
Sales_GRW
Q4 2019
Sales_GRW
Q1 2020
Sales_GRW
Q2 2020
Sales_GRW
Q3 2020
Sales_GRW
Q4 2020
Sales_GRW
Q1 2021
Sales_GRW
Q2 2021
Sales_GRW
Q3 2021
Sales_GRW
Q4 2021
Sales_GRW
Q1 2022
Sales_GRW
Q2 2022
Sales_GRW
Q3 2022
  44.74% -2.44% 2.45% 1.73% 1.51% 0.35% 2.33% 2.53% 0.89% 2.01% -0.22% 3.91% -1.30% 5.03% -6.21% 6.97% 1.13% 0.98% 2.97% 3.62% 0.54% 1.52% 3.38% -69.61%

 

I wanted to calculate Month over moth growth% as well like following:

GRW Sales_GRW
Aug 2016
Sales_GRW
Sep 2016
Sales_GRW
Oct 2016
Sales_GRW
Nov 2016
Sales_GRW
Dec 2016
Sales_GRW
Jan 2017
Sales_GRW
Feb 2017
Sales_GRW
Mar 2017
Sales_GRW
Apr 2017
Sales_GRW
May 2017
Sales_GRW
Jun 2017
Sales_GRW
Jul 2017
Sales_GRW
Aug 2017
Sales_GRW
Sep 2017
Sales_GRW
Oct 2017
Sales_GRW
Nov 2017
Sales_GRW
Dec 2017
Sales_GRW
Jan 2018
Sales_GRW
Feb 2018
Sales_GRW
Mar 2018
Sales_GRW
Apr 2018
Sales_GRW
May 2018
Sales_GRW
Jun 2018
Sales_GRW
Jul 2018
Sales_GRW
Aug 2018
Sales_GRW
Sep 2018
Sales_GRW
Oct 2018
Sales_GRW
Nov 2018
Sales_GRW
Dec 2018
Sales_GRW
Jan 2019
Sales_GRW
Feb 2019
Sales_GRW
Mar 2019
Sales_GRW
Apr 2019
Sales_GRW
May 2019
Sales_GRW
Jun 2019
Sales_GRW
Jul 2019
Sales_GRW
Aug 2019
Sales_GRW
Sep 2019
Sales_GRW
Oct 2019
Sales_GRW
Nov 2019
Sales_GRW
Dec 2019
Sales_GRW
Jan 2020
Sales_GRW
Feb 2020
Sales_GRW
Mar 2020
Sales_GRW
Apr 2020
Sales_GRW
May 2020
Sales_GRW
Jun 2020
Sales_GRW
Jul 2020
Sales_GRW
Aug 2020
Sales_GRW
Sep 2020
Sales_GRW
Oct 2020
Sales_GRW
Nov 2020
Sales_GRW
Dec 2020
Sales_GRW
Jan 2021
Sales_GRW
Feb 2021
Sales_GRW
Mar 2021
Sales_GRW
Apr 2021
Sales_GRW
May 2021
Sales_GRW
Jun 2021
Sales_GRW
Jul 2021
Sales_GRW
Aug 2021
Sales_GRW
Sep 2021
Sales_GRW
Oct 2021
Sales_GRW
Nov 2021
Sales_GRW
Dec 2021
Sales_GRW
Jan 2022
Sales_GRW
Feb 2022
Sales_GRW
Mar 2022
Sales_GRW
Apr 2022
Sales_GRW
May 2022
Sales_GRW
Jun 2022
Sales_GRW
Jul 2022
    26.97% -20.60% -2.25% 31.05% -24.59% 0.50% 27.93% -20.53% 2.73% 23.85% -21.13% 4.21% 25.06% -19.72% -3.39% 32.47% -22.54% 0.58% 27.28% -19.70% 1.86% 22.94% -20.09% 3.10% 28.06% -19.95% -2.39% 25.30% -19.43% 1.58% 27.62% -21.40% -1.17% 27.03% -17.56% 0.27% 25.04% -19.25% -3.64% 25.67% -18.71% 0.96% 36.44% -30.01% -1.62% 33.25% -20.27% 4.22% 27.63% -21.18% -1.24% 28.13% -20.26% -2.30% 30.86% -20.27% 2.20% 23.91% -21.71% 7.70% 26.27% -21.08% -3.83% 30.81% -20.96% 0.32% 28.00% -20.22% 3.66% 23.54% -21.28%

 

Please note I have implemented master calendar & as-of calendar also needs logic to create a flags for the same.

 

Thanks 

Vikas

 

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
Labels (1)
3 Replies
rubenmarin

Hi, I think I didn't undersstood it right. Why can't use?:

(Sum(Gross_SalesTest)-Before(Sum(Gross_SalesTest)))/Before(Sum(Gross_SalesTest))

LOAD *,
	Year(Month_Year) & ' Q' &Ceil(Month(Month_Year)/3) as Quarter
;
LOAD Date#(Month_Year,'MMM-YY') as Month_Year, 
     Gross_SalesTest
FROM
[.\Rolling Qtr test data.xlsx]
(ooxml, embedded labels, header is 2 lines, table is Sheet01);
vikasmahajan
Author

Hi Ruben,

I need  some thing like this :

Solved: Calculation to include the previous quarter for ea... - Qlik Community - 1787066

I am using custom extension reporting viz lib with 72 Dimensions & 14 + measures with 7 time period combinations.  there are lot of measures I have created like ytd,mat,mtd,qtd, roll 3 months,roll 12 months but stuck in growth calculation.

Hope you understand now.

Thanks

Vikas  

 

 

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
rubenmarin

Hi @vikasmahajan, sorry, I don't see why the Before() function wouldn't work, or an as-of calendar.

Another option I've used sometimes is to load all data adding one month or year to the date (in this case could be 3 months) and rename the values fields with _LM or _LY (or _LQ in this case), so the current data has access to last period data in the same dimensions. But with 72 dimensions I thik this solution can't be applied.

I would go to a solution similar to the as-of calendar or a date bridge.