2 Replies Latest reply: Sep 26, 2017 7:48 AM by Pim van der Kolk RSS

    Aggr monthly balances per year in Line Chart

    Pim van der Kolk

      Hi Developers,

       

      In thread Aggr monthly balances per year the formula RangeSum(Above(Count(Amount), 0, Num(Month))), provided by stalwar1, works perfectly when sorting on time chronologically.

      However, on the sorting tab in Qlik Sense you can't change the order of precedence for the dimensions. I want to visualize my data in a line chart, with the twelve months on the x-axis and a separate line for each year. This means that Month will be the first dimension and Year will be the second dimension (which will give a different order e.g. Jan '16, Jan '17, Feb '16, ....). The formula in the referred thread was based on Year as first dimension and Month as second dimension. So using Above() won't do the trick anymore. Aggregating the formula by year generates the total count per year, but only shows this total in the first month of that year. I need the cumulative count for each month per year.

      I've created some sample data below. How should I amend the formula to see the correct line chart?

       

      Thanks,

      Pim

       

       

      MonthYearOrders
      Nov2015511
      Dec20152001
      Jan20161567
      Feb20161832
      Mar20161905
      Apr20161798
      May20162019
      Jun20162088
      Jul20161989
      Aug20162101
      Sep20162100
      Oct20162085
      Nov20162120
      Dec20162209
      Jan20172139
      Feb20172250
      Mar20172241
      Apr20172268
      May20172239
      Jun20172278
      Jul20172325
      Aug20172317