topic Graphing YTD values for each month in QlikView

Graphing YTD values for each month

Mon, 01 Nov 2010 18:44:54 GMT

Hi everyone,

Currently, I have sales data by month. I want to use this to create a line chart with YTD sales by month (Jan YTD, Feb YTD, etc...). In otherwords, each month includes all the sales from the previous months. I tried the accumulation option, but this doesn't work for indicators such as price.

Does anyone have any suggestions on how I might go about doing this in Qlikview?

Thanks,

Iaroslav

Graphing YTD values for each month

johnw — Mon, 01 Nov 2010 18:51:03 GMT

I'd probably handle it by generating an AsOf table.

AsOfMonth, Month
2010 Jan, 2010 Jan
2010 Feb, 2010 Jan
2010 Feb, 2010 Feb
2010 Mar, 2010 Jan
2010 Mar, 2010 Feb
2010 Mar, 2010 Mar
etc.

Then just use AsOfMonth instead of Month in your chart.

Graphing YTD values for each month

Mon, 01 Nov 2010 20:11:12 GMT

Hi John,

Thank you very much for your reply. This solves my issue. Now I am trying to do something slighly more complex; I want to plot both monthly and YTD values by month on the same chart. Is it possible to use "set analysis" in my expression to accomplish this?

I tried something like this: sum({$<Month = AsOfMonth>} Volume)

I am pretty sure there is an issue with my syntax, because I cannot get any data using this expression (AsOfMonth is the dimention I am using in the chart).

Thanks,

Iaroslav

Graphing YTD values for each month

johnw — Mon, 01 Nov 2010 20:59:12 GMT

You can expand the AsOf table with an AccumulationType field (or call it whatever makes sense for your application):

AsOfMonth, AccumulationType, Month
2010 Jan, None, 2010 Jan
2010 Feb, None, 2010 Feb
2010 Mar, None, 2010 Mar
etc.
2010 Jan, YTD, 2010 Jan
2010 Feb, YTD, 2010 Jan
2010 Feb, YTD, 2010 Feb
2010 Mar, YTD, 2010 Jan
2010 Mar, YTD, 2010 Feb
2010 Mar, YTD, 2010 Mar
etc.

Then add AccumulationType as a dimension, and you should get both lines plotted.

Off hand, the syntax you show looks right to me, so obviously I'm missing something. You could probably do sum(if(Month=AsOfMonth,Volume)), but it would be more efficient to use a data solution like the one above.