growth over previous Year

[nevilledhamsiri]

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

[nevilledhamsiri]

Can any one please see to this?

Thanks

Neville

[Brett_Bleess]

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

[Kushal_Chawda]

Please share more sample data which contains more products and branch 

[nevilledhamsiri]

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