Year to Year Change in Line Graph

[Stars]

I've read several other posts but can't find anything that's working for me.  I want a line graph of Change in Sales %.  I want 2020 to show: (Sales(2020) - Sales(2019))/Sales(2020).  But I can't figure out how to reference a year within my equation when it's graphing by year already.

[Taoufiq_Zarra]

@Stars  like

=(Sum({$<Year={'2020'} >} Sales)-Sum({$<Year={'2019'} >} Sales))/Sum({$<Year={'2020'} >} Sales)

[anthonyj]

Hi Stars,

It sounds like from your question that you have may have another dimension that you're calculating over. This is something I've come across before. Qlik can easily handle the comparison of one year vs another year as Taoufiq has pointed out, but if you add a dimension to the mix it breaks. So if you wanted a chart with July 19 and July 20 comparison along with all the other months Qlik wouldn't allocate both these against one dimensional value of July 20 so the percentage wouldn't work. Qlik needs a way of mapping July 19 to July 20, Aug 19 to Aug 20 etc...

I've been playing with this script to match up the months. It does work however it leaves you with blank dimensions at the beginning. I'll have to work on that one more.

If your dimension is months then you can shift the calculation by using above( ) and 12 as the second parameter to match the months up. Like this:

Dimension:
SalesMonth

=(Sum({$<Year={'2020'} >} Sales)-above(Sum({$<Year={'2019'} >} Sales),12))/Sum({$<Year={'2020'} >} Sales)

If someone has another way of doing that in the UI I'd love to hear it because we have had to do it quite often in Excel in the past.

Just a note on variance. This may be different from what you're trying to accomplish and if so just ignore but to calculate variance it's (New - Target) / Target. So in this case (2020 - 2019) / 2019 but if that's not what you're going for then just ignore that.

Thanks and Good luck

Regards

Anthony