5 Replies Latest reply on Sep 8, 2016 11:32 AM by Darren Lewis

    Previous_Value not Summing

    Darren Lewis

      Hello Tableau Wizards! I cannot figure this out!!! I have sparsely populated data that is being asked to be seen on a monthly basis. The 'Amount Monthly' is propagating the 'Amount' value along the months. The 'Amount' is not a running sum because each month's values are already aggregated.

       

      Formula is:  IFNULL(SUM([Amount]),PREVIOUS_VALUE(SUM([Amount])))

       

      This all works fine except for the Grand Total.

       

      previous value not summing.png

        • 1. Re: Previous_Value not Summing
          Joe Oppelt

          The [Amount Monthly] grand total is obeying the calc.  That's what it's going to do.

          1 of 1 people found this helpful
          • 2. Re: Previous_Value not Summing
            Darren Lewis

            Thanks Joe. Sorry I wasn't clear with my question. I recognized that the Grand Total is following the calculation logic. I guess I should have asked, how can I get the correct numbers in the Grand Total line. Thoughts?

             

            Thanks again!

            • 3. Re: Previous_Value not Summing
              Joe Oppelt

              I started working on that right after I posted my previous reply.

               

              You have to make your own totals.

               

              See attached.

               

              Sheet 5 is a dup of sheet 4.  Then I added two new table calcs:  [Total Amount] and [Total Amount Monthly].  I changed them to do Specific Dimensions, and to restart every month.   Ignore what shows in the "Grand Total" chunk of the viz.  You will see the value showing up for each [Table], and they have the values you need.

               

              Sheet 6 is a dup of sheet 5.  Then I moved the two old measures to the detail shelf.    And I killed the TOTALS from "Analysis".  All that leaves are the two Total calcs displayed.

               

              Sheet 7 is a dup of sheet 6.  Then I moved [Table Name] to the detail shelf.  (We still need it on the sheet to run through the table properly.)  Now you see 4 values of each Total for each month.  That's how Tableau works.


              Sheet 8 is a dup of Sheet 7.  I created a calc called index.  I put it in the filter shelf and changed the table calc to Specific Dimensions and restart every month.  Now it's "walking" the table the same way the Total calcs are.  I edited the filter just to get value = 1.  Only get one index from each cell.


              End result is a new sheet that gets your totals for you.

               

              Sheet 4A is a dup of Sheet 4 without the Totals from "Analysis".

               

              Dashboard 1 positions the two sheets so that make one viz for the user.

               

              Special note:  You will notice on the interim sheets that Tableau is labeling [Total Amount] as "Total Amount along Month of ..."  Tableau does that.    Now, I'm not totally familiar with 10.0 yet, but I see that it's not doing it to [Total Amount Monthly].    Not sure why.  In this TWBX we could ALIAS that correctly, but if, in your actual workbook, you are using a data extract, I think we still cannot do ALIAS on fields from TDEs.  So in the dashboard I hacked up a mask of a blank container that I colored white and floated it over the extraneous words, and that's why you don't see "along ..." displayed there.  (That's just window dressing.)


              Anyway, when it comes to "Grand Totals" of calcs, it can get a bit messy, and I just make my own the way I want them and artistically display it on a dashboard like this.

              1 of 1 people found this helpful
              • 4. Re: Previous_Value not Summing
                Joe Oppelt

                Of course, you don't need all those interim sheets.  I just left a trail of sheets for a step-by-step demonstration of that I did in there.

                • 5. Re: Previous_Value not Summing
                  Darren Lewis

                  JOE!!!!!

                   

                  This is amazing. What an awesome detailed response. Thank you very much.