3 Replies Latest reply: Nov 1, 2010 4:59 PM by John Witherspoon RSS

    Graphing YTD values for each month

      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
          John Witherspoon

          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

              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
                  John Witherspoon

                  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.