4 Replies Latest reply on Feb 13, 2012 8:43 PM by Andrew Fisher

    Running sum over period but only show last value

    Andrew Fisher

      Hi all,

       

      Not sure how to do this as the way I attempted to do it didn't work and I understand what tableau's attempting to do but don't know how to get around it or approach it differently.

       

      So here's what I've got and what I'm trying to achieve.

       

      For every day across a month I have a target value for number of orders. These are calculated out ahead of time. For each day as I move through the month we then get the number of orders (up to yesterday as this is done at 1am) that were made yesterday.

       

      I can then show how we're tracking to our target by showing the target and the actual orders next to each other using running sums over the course of the month. That works fine.

       

      In a cross tab then I can show what our current "drift" is - ie whether we are ahead or behind target by doing a running sum on the differences between actuals and target for each day. That all works fine too.

       

      This all works perfectly at the month view without any problems.

       

      What I'm trying to do though is produce a dashboard we can use internally that distills this down to just the number for where we are in the month to date.

       

      EG:

       

      date     1/1     2/1     3/1     4/1

      orders     10     11     12     13    

      target     10     5          10     20

      diff           0     6          2     -7

      drift          0     6          8       1

       

      So in my dashboard / worksheet then I'd have simply "Yesterday" as the date and it would show (from example above)

       

      date     4/1

      orders     13

      target     20

      diff          -7

      drift          1

       

      When I try and do this you can imagine what happens - because my partition only spans 1 day obviously my drift value simply shows -7 as that's what it is for the day in question.

       

      So to my question then - how can I calculate a value like this which is a running sum of the differences over a period of time longer than the unit of time that I'm actually looking at - ie Month to date calculation?

       

      Cheers

      Andrew