2 Replies Latest reply on Jun 24, 2015 1:53 AM by Ashish Verma

    Running Total using a parameter to select Date

    Ashish Verma

      Tableau experts Jonathan Drummey Shawn Wallwork Jim Wahl  Need your help here

      Please don't mind the tagging as the issue I am into is critical to my requirement.

       

      Sample data and workbook attached.

       

      I have Month, Day, Inventoryadded/deleted, Stock in Inventory columns in my database.
      I want to show the running sum of the inventory based on a parameter.

      Values in the Inventory added/deleted column are cr,db and NA. cr means credited, db - debited and NA means no change in status.
      If the value is cr then stock should get added to the previous running total, if value is db then it should get subtracted from the previous value, if value is NA then it should show previous value. I tried and was able to achieve this.

      I am pasting the formula here :

       

      IF FIRST()=0 then sum([Stock in Inventory]) else

      (

      IF ATTR([Inventory added/deleted])='cr'

      then PREVIOUS_VALUE(RUNNING_SUM(SUM([Stock in Inventory])))+SUM([Stock in Inventory])

      elseif ATTR([Inventory added/deleted])='db'

      then PREVIOUS_VALUE(RUNNING_SUM(SUM([Stock in Inventory])))-SUM([Stock in Inventory])

      elseif ATTR([Inventory added/deleted])='NA'

      then PREVIOUS_VALUE(RUNNING_SUM(SUM([Stock in Inventory])))

      end

      )

      end

       

      So far no problem. As soon as I proceed to the next step, I start facing problems.Let me explain what should be the next steps :

      Now I want a parameter where if the user selects Month=February then the view should show running sum of the inventory on the last day of that month. In my case the output should be 2100(28th feb).

      When I am capturing the parameter in my calc field then it is showing me the stock Inventory's cr,db or NA value for that day. Tableau starts calculating the running total from 0 for the filtered rows.

      Hope I have explained the situation in detail.

       

      Message was edited by: Ashish Verma

        • 1. Re: Running Total using a parameter to select Date
          Jim Wahl

          Hi Ashish ---

           

          The main issue is that you can't use a regular filter, since the value you want (Jan 31, Feb 28, ...) depends on previous values. The trick is to use a table calc filter --- which rather than filtering values, hides them.

           

          You can turn most regular filters into table calc filters by wrapping them in a LOOKUP(ATTR([dimension you want to filter on]), 0). LOOKUP(..., 0) is essentially a no-op, as it's looking up the value of the current row, "0".

           

          Table calcs operate on aggregates, and here I've wrapped the dimension in an ATTR(). Assuming the worksheet is at the granularity of the dimension, MIN(), MAX(), ..., would give the same result. ATTR() provides a little bit of error checking in the case where the granularity isn't the same and there are multiple values -- in this case ATTR() returns * where MIN(), MAX() will return a valid-looking value. On the other hand ATTR() is a bit slower than MIN() and MAX(). ...

           

          Here's how it would look in your example:

          Show only Last Day in Month =

          // Last Day in month = first day of the next month - 1 day. 
          IF LOOKUP(ATTR([Month]),0) == [Select Month] AND 
             LOOKUP(ATTR([Day]),0) == MIN(DATEPART('day', DATEADD('day', -1, DATEADD('month', 1, DATE("1-" + [Select Month] + "-2015")))))
          THEN 1
          ELSE 0
          END
          

           

          This assumes you have a parameter [Select Month] which is January, February, ...  To find the last day in the month, I'm creating a date field with the month given by the parameter, adding one month to it and subtracting one day. If the user selects February, I create a date #1-February-2015# add one month #1-March-2015# and subtract one day = #28-February-2015#.

           

          This could probably be simplified with a LOD calc in Tableau 9.

           

          Depending on how much data you have, you may also want to actually filter some of the rows for performance, and you could probably do this by looking for the closest NA value.

           

          Incidentally, I think you can simplify your first calculation a bit:

          Stock Inventory Running Total =

          PREVIOUS_VALUE(0) + 
          ZN(SUM(
          CASE [Inventory added/deleted] 
          WHEN "NA" THEN 0
          WHEN "cr" THEN    [Stock in Inventory]
          WHEN "db" THEN -1*[Stock in Inventory]
          END))
          

           

          PREVIOUS_VALUE(x) will return x on the first row, so you don't need the IF FIRST() == 0.

           

          2015-06-23_22-25-29.png

           

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Running Total using a parameter to select Date
            Ashish Verma

            Thanks Jim. You guys rock.