topic Re: Sum vs Previous Sum By Years, % in QlikView

Sum vs Previous Sum By Years, %

Death4Free — Thu, 27 Sep 2018 07:10:07 GMT

Hi! How can I calculate measure in pivot table - Sales Vs Prev. sales, % ( there could be more years, and i have Calendar Master if it helps)?

	2016		2017		2018
	Sales	Sales Vs Prev. sales, %	Sales	Sales Vs Prev. sales, %	Sales	Sales Vs Prev. sales, %
dec	10	100%	12	120%	10	83%
jan	9	100%	6	66%	8	133%
feb	15	100%	16	106%	18	113%

Re: Sum vs Previous Sum By Years, %

juraj_misina — Thu, 27 Sep 2018 08:07:01 GMT

Hi,

you can use Before() and Alt() functions for this.

Sales = Sum(Sales)

Sales vs Prev. sales, % = Alt(Sum(Sales)/Before(Sum(Sales)), 1)

Before wil calculate Sum(Sales) using previous dimension value (previous year in your case), but in the first column it will return null (because there's no previous value), therefore you need to combine it with Alt(), witch returns first valid value from a list of parameters.

Hope this helps.

Juraj

Re: Sum vs Previous Sum By Years, %

Death4Free — Thu, 27 Sep 2018 10:12:24 GMT

Thank you for so fast answer!