2 Replies Latest reply on Apr 12, 2011 5:14 AM by Tom Brown

    Calculate difference from previous period (and then remove previous period from view)

    Tom Brown

      Hi,

       

      I am trying to calculate the difference from the previous month or year in my view.  Once I have the calc, I then want to remove the previous period from the view as I'm only interested in the movement, and I don't want another column showing 'no movement' (the column the calc is based on).

       

      Removing the column with a filter breaks the calc.

       

      I know I can hide the column, but this is essentially hardcoded - and I actually want to show the current period (rolling from live data) - so I would need to condition the 'hiding' of the previous period, something I don't think is possible.

       

      Anyone have a solution for this?  example twbx attached.

       

      Cheers

       

      Tom

        • 1. Re: Calculate difference from previous period (and then remove previous period from view)
          Joe Mako

          If you change your calc field from:

           

           

          SUM([Sales]) - LOOKUP(SUM([Sales]))


           

          to:

           

           

          SUM([Sales]) - LOOKUP(SUM([Sales]),-1)


           

          This will cause the value for 2009 to be NULL, and then you can ctrl-drag the pill onto the filter shelf, selecting Special, and Non-null-values.

           

          Without the offset argument in the calculation, it enables the Relative To option in the context menu for the calculation's pill, which does not seem useful based on the layout and the filter you have applied. Without the offset argument, the values for 2009 are zero and not NULL because it is basically saying: "Return the difference between this year and year selected in context menu" so for the 2009 values, it is subtracting the 2009 value from the 2009 value, equaling zero. With "-1" set for the offset argument, it is basically saying: "Return the difference between this year and the previous year" so when have the data filtered to just 2009 and 2010, when evaluating the calculation for 2009 values, the calculation returns NULL because there is no previous year available. So without the offset argument, you could filter out values where it is zero, but that could filter inadvertently filter out values you do not want to filter, and with the argument, you can filter on NULL values, ensuring that all actual number are returned.

          • 2. Re: Calculate difference from previous period (and then remove previous period from view)
            Tom Brown

            Thanks as usual Joe - the trick I needed was filtering based on null values.  I guess filters based on table calculations have to be applied after the calculation has been done - seems obvious now!

             

            Cheers

             

            Tom