topic Year over Year Calculation in QlikView

Year over Year Calculation

Anonymous — Fri, 08 Dec 2017 13:45:47 GMT

Hi,

I am trying to show year on year(YoY) comparison in a straight table. But when the sum of a particular category is 0 in the previous year, it's not showing any value, even though the next year has a value

E.g.

2016 2017

0 100

I expect to see 100% increase

I tried using this formula but it's not working properly

if(Sum({$<Year = {2016}>}[USD Amount]) = 0, '100%',

(fabs(Sum({$<Year = {2017}>}[USD Amount]))-fabs(Sum({$<Year = {2016}>}[USD Amount])))/fabs(Sum({$<Year = {2016}>}[USD Amount])))

The formula works partly as it also shows 100% when 2017 value is 0

I need to add a condition where if both the years are 0, then it's 0%, otherwise calculate.

Any help is appreciated

Thanks,

Sunny

Re: Year over Year Calculation

sunny_talwar — Fri, 08 Dec 2017 14:01:18 GMT

May be try this

If(Sum({$<Year = {2016}>}[USD Amount]) = 0,

If(Sum({$<Year = {2017}>}[USD Amount]) = 0, '0%', '100%'),

(fabs(Sum({$<Year = {2017}>}[USD Amount]))-fabs(Sum({$<Year = {2016}>}[USD Amount])))/fabs(Sum({$<Year = {2016}>}[USD Amount])))

Re: Year over Year Calculation

Anonymous — Mon, 11 Dec 2017 07:39:44 GMT

Thanks dude, it works