topic Re: Prior Quarters & month growth% calculation in pivot table in App Development

Prior Quarters & month growth% calculation in pivot table

vikasmahajan — Tue, 27 Sep 2022 07:12:03 GMT

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

Re: Prior Quarters & month growth% calculation in pivot table

rubenmarin — Fri, 23 Sep 2022 12:57:32 GMT

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

Re: Prior Quarters & month growth% calculation in pivot table

vikasmahajan — Fri, 23 Sep 2022 14:08:29 GMT

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

Re: Prior Quarters & month growth% calculation in pivot table

rubenmarin — Mon, 26 Sep 2022 06:23:21 GMT

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.