Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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
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.