Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Members,
If I'm to get the growth on each latest Year as shown in column G (Coloured in yelow), what expression is possible?
TR_YEAR | BRANCH | PRODUCT | TR_MONTH | SUM(PREMIUM) | //(SUM(PREMIUM)/before(SUM({<Year>}PREMIUM))-1)*AVG(1) (Sum(PREMIUM)/ABOVE(Sum({<TR_YEAR>}PREMIUM)) - 1) * Avg(1) //Avg(Aggr(Sum(Measure)/Above(Sum(Measure)), Year)) //or //Avg(Aggr(Sum(PREMIUM)/Above(Sum(PREMIUM)), TR_YEAR,TR_MONTH)) | Growth from latest Year Vs Previous Year |
2018 | RA | MOTOR | Jan | 43,892 | - | |
2018 | RA | MOTOR | Feb | 27,390 | -38% | |
2018 | RA | MOTOR | Mar | 42,633 | 56% | |
2018 | RA | MOTOR | Apr | 68,824 | 61% | |
2018 | RA | MOTOR | May | 54,516 | -21% | |
2018 | RA | MOTOR | Jun | 71,924 | 32% | |
2018 | RA | MOTOR | Jul | 55,227 | -23% | |
2018 | RA | MOTOR | Aug | 27,392 | -50% | |
2018 | RA | MOTOR | Sep | 51,843 | 89% | |
2018 | RA | MOTOR | Oct | 38,746 | -25% | |
2018 | RA | MOTOR | Nov | 59,101 | 53% | |
2018 | RA | MOTOR | Dec | 59,356 | 0% | |
2018 | Total | 600,844 | - | |||
2019 | RA | MOTOR | Jan | 55,549 | - | 27% |
2019 | RA | MOTOR | Feb | 41,576 | -25% | 52% |
2019 | RA | MOTOR | Mar | 38,623 | -7% | -9% |
2019 | RA | MOTOR | Apr | 72,562 | 88% | 5% |
2019 | RA | MOTOR | May | 74,435 | 3% | 37% |
2019 | RA | MOTOR | Jun | 65,511 | -12% | -9% |
2019 | RA | MOTOR | Jul | 53,412 | -18% | -3% |
2019 | RA | MOTOR | Aug | 63,175 | 18% | 131% |
2019 | RA | MOTOR | Sep | 57,960 | -8% | 12% |
2019 | RA | MOTOR | Oct | 60,642 | 5% | 57% |
2019 | RA | MOTOR | Nov | 52,491 | -13% | -11% |
2019 | RA | MOTOR | Dec | 44,053 | -16% | -26% |
2019 | Total | 679,989 | - | 13% | ||
2020 | RA | MOTOR | Jan | 58,549 | - | 5% |
2020 | RA | MOTOR | Feb | 26,165 | -55% | -37% |
2020 | RA | MOTOR | Mar | 67,332 | 157% | 74% |
2020 | RA | MOTOR | Apr | 38,466 | -43% | -47% |
2020 | RA | MOTOR | May | 66,801 | 74% | -10% |
2020 | RA | MOTOR | Jun | 38,034 | -43% | -42% |
2020 | RA | MOTOR | Jul | 45,568 | 20% | -15% |
2020 | Total | 340,915 | - | |||
Total | 1,621,748 | - |
Thanks
Neville
Can any one please see to this?
Thanks
Neville
You can try the following Design Blog post, but I know there are other Community posts regarding year over year comparisons, have you searched and reviewed those too? It would likely be most helpful if you attach your QVW file for things, as people are going to need to see the data model most likely in order to know how to structure the expression...
https://community.qlik.com/t5/Qlik-Design-Blog/Year-over-Year-Comparisons/ba-p/1462927
Regards,
Brett
Please share more sample data which contains more products and branch
Hi Kush,
Could you please check the data in the table. When there are three Years involved with Months may be in certain Years, some Months are not yet passed like being in July.What I need is to calculate the growth in the latest Year (Monthly) as against the immediate preceeding Year. In the case of Year is not yet completed may be half way through, then the growth calculation should end up the latest Month as against the Immediate previous Year.Also the subtotal under each year, should do the same calculation (Hint; both year having current & previous Year fully completed). In the case of latest Year gone up to say July & last Year we have data for twelve Months, the then the total calculation should be the comparision between the sum of seven Months Vs last Year same seven Months. There are quite a few more Branches with the same periods involved. Please suggest the formula to handle this