topic Re: Year over Year expression over time in QlikView

Year over Year expression over time

Anonymous — Thu, 29 Nov 2018 15:32:35 GMT

Hello,

I am trying to calculate the YoY change of a metric over time in a straight table. The table trends the metric over time using a Month Year field (MMM YYYY). I'm using the following expression to calculate the difference between two months (i.e. Oct 2018 vs Oct 2017):

sum({[Trending]<KEY={'ESTH'}>}Value)-
above(sum({[Trending]<KEY={'ESTH'}>}Value),12)

This formula is giving me the result I expect. However, it only does so when I have all data points between Oct 2017 and Oct 2018 selected and only brings back results for Oct 2018. I'm guessing this has to do with how the above function works but am hoping there is an alternative to get this metric to display without selecting the previous year's data point.

I'm trying to create a view that just show's the current year's months and their YoY change. Any help on how to accomplish this would be great!

Re: Year over Year expression over time

sunny_talwar — Thu, 29 Nov 2018 15:36:47 GMT

Can you try this

RangeSum(
Sum({[Trending]<KEY = {'ESTH'}>} Value),
-Above(Sum({[Trending]<KEY = {'ESTH'}, [Month Year field]>} Value), 12)
) * Avg({[Trending]<KEY = {'ESTH'}>} 1)

Re: Year over Year expression over time

Anonymous — Thu, 29 Nov 2018 15:45:08 GMT

Thank you for such a quick response. This appears to be working!

I have a follow-up question on how I can modify this to show a rolling 12 month view based on the Month Year selected. I am doing this in other expressions by defining the Month Year field in the set analysis as follows:

[Month Year]={">=$(=date(addmonths(max([Month Year]),-11),'MMM YYYY'))<=$(=max([Month Year]))"}

Is there a way to modify the solution you provided me to include this so it shows the YoY for the past 12 months?

Thanks again!

Re: Year over Year expression over time

sunny_talwar — Thu, 29 Nov 2018 15:55:20 GMT

Try this

RangeSum(
Sum({[Trending]<KEY = {'ESTH'}, [Month Year]={">=$(=date(addmonths(max([Month Year]),-11),'MMM YYYY'))<=$(=max([Month Year]))"}>} Value),
-Above(Sum({[Trending]<KEY = {'ESTH'}, [Month Year]>} Value), 12)
) * Avg({[Trending]<KEY = {'ESTH'}, [Month Year]={">=$(=date(addmonths(max([Month Year]),-11),'MMM YYYY'))<=$(=max([Month Year]))"}>} 1)