1 2 Previous Next 15 Replies Latest reply on Aug 24, 2016 6:37 AM by Gerardo Varela

    Running (Sub)Totals

    Jakob Sebens

      Hi Community,

       

      After some puzzling with subtotals and running totals, i was struggling finding the right functionality in Tableau.

       

      I have a table with OrderNr, OrderDate and OrderAmount. I created a table in Tableau with the Dates in the column (on year, month) and the order amounts as values. The running total should be added to get a sum till the end of the year, per year. That way i can see what my end balance is at the end of the year.

       

      I got a couple of solutions for this, first is adding subtotals. That way: The layout is perfect and i have order amount values for each month, and at the end of the year, i have a total. However, the total is only the total for the specific year. What i want can be accomplished with a running_sum. Counting all the data up to a certain month. The problem is, the layout will be bad, since the value will be shown for every month.

      OrderAmountTest.png

      Take a look at the added screenshots and the workbook to see what i really try to accomplish:

       

      Kind regards,

       

      Jakob

        • 1. Re: Running (Sub)Totals
          Naveen Agarwal

          Can you clarify what exactly you are trying to accomplish?

           

          Your views show Year/Quarter on the Columns, not months. Secondly, the Running_Sum is being used on the number of records, not on the Order Amount.

           

          Do you want to show both YTD total Order Amount as well as a running total by Month?

          1 of 1 people found this helpful
          • 2. Re: Running (Sub)Totals
            Jakob Sebens

            Hi, Thanks for your reply.

             

            As time passes by, we receive orders with an amount of money. I want to create a table where is shown what the amount of orders are per month (Sum), and i want to show the total of each year at the end after December. The total should be a running total, so in 2013, the data from 2012 counted with the data from 2013.

            • 3. Re: Running (Sub)Totals
              Ryan Colliver

              My take on your data set. You might also just create simple text of the annual order amount to add to the dashboard.

               

              Hope this helpshttps://public.tableau.com/static/images/Or/OrderAmountTest_rc/rc3/1.png

              • 4. Re: Running (Sub)Totals
                Gerardo Varela

                Hi Jakob,

                    I think you are looking for the attached vs 9.3?  I've never done a running sub total, so thank you for the nice exercise! Pay careful attention to each table calculations compute using of each of the table calc's. Here is a really good blog about table calc's:

                 

                Customizing Grand Totals – Part 1 | Drawing with Numbers

                 

                Regards,

                Gerardo

                2 of 2 people found this helpful
                • 5. Re: Running (Sub)Totals
                  Naveen Agarwal

                  Are you looking for something like this? In this view, if you expand to Months, you will get the Sum or Order Amount and a Running Sum at the month level.

                   

                  • 6. Re: Running (Sub)Totals
                    Jakob Sebens

                    I really like your answer, but i need the subtotal on a year level, and not on a month level. I maybe am on the wrong way trying to reproduce Excel sheets.

                    • 7. Re: Running (Sub)Totals
                      Jakob Sebens

                      Fantastic! That is what i want, it looks really good, thanks!

                       

                      I really love how the community is thinking with people, and over time, when i gain more experience i can give the exact knowledge back!

                       

                      Thank you, Gerardo!

                      • 8. Re: Running (Sub)Totals
                        Jakob Sebens

                        I got to tell you this: I've been trying to get this solution done for some time now, and it was not working. But now that i see your solution, i only get more and more energy to fully understand what you did  instead of only copying it. Thank you so much for your provided answer. I absolutely love it.

                        • 9. Re: Running (Sub)Totals
                          Gerardo Varela

                          You're very welcome!

                           

                          Regards,

                          Gerardo

                          • 10. Re: Running (Sub)Totals
                            Jakob Sebens

                            Oh hey, so i tried implementing your solution in my real work document, but i ended up with this and i can't seem to get the totals in the amount row: Numbers.png

                             

                            I exactly copied your formula's and gave the dimensions the proper calc (acros pane or acros table etc.) what am i doing wrong?

                            • 11. Re: Running (Sub)Totals
                              Gerardo Varela

                              Not really sure from the screenshot you provided.  A packaged workbook with a sample of your data would be best. Next best thing would be a screen shot similar to the below showing your marks card, the formula, and the compute using settings of said formula.

                               

                               

                              Regards,

                              Gerardo

                              2 of 2 people found this helpful
                              • 12. Re: Running (Sub)Totals
                                Jakob Sebens

                                It appears that you can fiddle with computations in Tableau, whenever i take the measure "Amount", right click it, you got two options: Either selecting your calculation in "Select table calculation" and "compute using". If i select "compute using" first, and after "Select table calculation" i get different results. I cannot see if the "compute using.." still applies or not, but it appears so. I keep fiddling with these calculations till i find what is going wrong. I couldn't recreate the same outcome you had in the workbook you send to me so i do something wrong i guess. Perhaps it is an order of applying measures or computations? If so, please enlighten me,

                                 

                                Thank you very much for your time and effort in this matter.

                                 

                                Jakob

                                 

                                EDIT1: (I recreated the fields exactly as in your workbook but the only thing i can think of is the order of how you build things up, which shouldn't make sense of course. I just have the feeling that if i use the "compute using" option but later override it by using the table calculation option, the "compute using" will still be active without me noticing it. That way, the calculations might be different while it appears to be the same. )

                                 

                                EDIT2: (See the attached screenshot of all the formula's i made in my sheet. I added the value in text if i click on the: "Compute using" option. Keep in mind that for AMOUNT i changed the "pane across then down" to "Pane Across" so there is no difference with your provided example)

                                Numbers.png

                                EDIT3: (I see a difference within the priority of the calculated fields in my data, take a look at this:

                                Difference.png

                                The field "Amount" is at the last place in the upper example, the one below has "First()=LAST() as last place in the example below)

                                • 13. Re: Running (Sub)Totals
                                  Gerardo Varela

                                  Looks like [Start] in your nested calculation Amount compute using is not set correctly.  Make sure all of your nesting is correct. Edit your table calculation Amount:

                                   

                                  Check that [FIRST()=LAST()] is set along pane across.

                                  Now go to your other nested table calculation  by clicking the drop down arrow.

                                  You should see the following.  Now choose Running and make sure the compute using is set to table across.

                                   

                                   

                                  Do the same for start which I'm guessing is where the problem is at.  Make sure it's set to compute using pane across.

                                   

                                  I hope that clears things up because after that I'm not sure whats wrong without seeing your workbook. 

                                   

                                  Regards,

                                  Gerardo

                                  2 of 2 people found this helpful
                                  • 14. Re: Running (Sub)Totals
                                    Jakob Sebens

                                    Hi Gerardo,

                                     

                                    YES! You are absolutely a hero! I recreated the nested calculations (I did not look at them since they where not visible in the Measure value names since the where filtered out) and it works quite well. I love your solution, and thank you for your time and effort in solving this matter!

                                     

                                    Kind regards,

                                     

                                    Jakob

                                    1 2 Previous Next