1 Reply Latest reply: Jun 22, 2012 5:00 AM by Stefan Wühl RSS

    YTD which resets to 0 at each yearstart

      Hi,

       

      I need to display in a pivottable *and* a graph YTD information, which resets to 0 after each Yearstart (January).

      I thought this would be easy to do, but alas I've not been able to do it.

       

      Can anyone help me or suggest alternatives?

       

      Kind Regards,

       

      Dion

       

      (Numbers are scrambled)

        • Re: YTD which resets to 0 at each yearstart
          Stefan Wühl

          If you say, that you want to reset to zero, I assume you want to aggregate the numbers over the year (YTD).

          This is what I can't see right now, it seems you just sum the numbers per month.

           

          If you want to aggregate the numbers (YTD), you could use a rangesum() function, but you also need to define your columns segments appropriately. So you could try:

           

          =aggr(rangesum(above(Sum({<MetricNumber={'2.4.2.2.'}>} Numbers),0,RowNo())),Year,NumMonth)

           

          as expression in your table / line chart (charts in the container).

           

          If for any reason your current results are ok, but you want zero on every January, try

           

          =if(NumMonth=1, 0, Sum({<MetricNumber={'2.4.2.2.'}>} Numbers) )

           

          Hope this helps,

          Stefan