Dynamic Year Over Year

Wed, 22 Jul 2009 22:22:18 GMT

I'm trying to create a dynamic year over year chart. I'd like to compare total spend & volume for all years in my data set to the previous year to get a delta and % delta. Has anyone been able to achieve this?

Dynamic Year Over Year

Wed, 22 Jul 2009 23:25:10 GMT

Here is an approach for a solution. It gets the answer you're looking for. The only down-side is that it DOES matter which way you sort the years...so the sort could not be dynamic...if that matters to you.

My approach uses the Above() (or Below()) functions to determine the Sum of Sales for the previous year. I found some documentation of how to do it in Set Analysis, but you have to have 1 year selected. Where you wanted to do multiple years, this was the only approach I could think of.

So, this example has the years sorted in descending order and I use the BELOW() function to get the value for LY values.

I created 2 graphs, one for spend, and one for qty...simply because the numbers were orders of magnitude different, and I needed the slipt axes to show percent change values.

Hopefully this gets you started in the right direction.

Regards,

sjprows

topic Dynamic Year Over Year in QlikView

Dynamic Year Over Year

Dynamic Year Over Year