6 Replies Latest reply on Dec 3, 2015 11:11 AM by Celia Yan

    how to pass a running total to a detail row?

    Celia Yan

      Hi,

       

      Hope someone here can help me with mixing running total numbers and detail row numbers:

       

      I have monthly contract revenue and delivered revenue counts as below as marked in Yellow. Before, we bill our clients based on delivered revenue on a monthly basis. Recently we decide to bill clients based on contract revenue for the first 11 months and accumulate all differences b/w contract and delivered counts together to Dec. Column E shows the cumulative difference by month. Column F is the new billed revenue based on:

      If month <> 12 then Billed revenue = Contract revenue

      If month = 12 then Billed revenue = Contract revenue of Dec + Running_total (delivered - contract) along month

       

       

      Sample Data.png

       

      In Tableau file attached, I tried to add the running total of diff. to the contract revenue of Dec but got formula errors.

      Formula Errors.png

       

      Is there any way to fix this? Really appreciated for your help in advance!

       

      - Celia

        • 1. Re: how to pass a running total to a detail row?
          Shawn Wallwork

          Celia, you can write the calc this way:

           

          IF MAX(DATEPART('month', [Month of Week])) <> 12 THEN SUM([Contract Revenue])
          ELSE SUM([Contract Revenue]) + WINDOW_SUM(SUM([Diff.]), FIRST(), 0) END
          

           

          And it will give you the same values as you have in column F in your Excel file. Is that what you are looking for? If not, what version of Tableau are you using. Thanks,

           

          --Shawn

          • 2. Re: how to pass a running total to a detail row?
            Celia Yan

            Hi Shawn,

             

            Really appreciated for your help!! It is exactly what I'm looking for and works well now.


            I'm trying to understand your fix better, especially for the use of max(). It seems that datepart() generates non-aggregated arguments in IF statement. Max() can help convert the result of datepart() to aggregated argument and also point to 12 for this case.


            What if contract revenue and cum diff. need to be summed up for both of Nov and Dec? Is there any other formula that can convert datapart () from non-aggregated to aggregated?

             

            Please advise. Thank you!

             

            - Celia

            • 3. Re: how to pass a running total to a detail row?
              Shawn Wallwork

              It would be something like this:

               

              IF MAX(DATEPART('month', [Month of Week])) <= 11 THEN SUM([Contract Revenue]) 
              ELSE SUM([Contract Revenue]) + WINDOW_SUM(SUM([Diff.]), FIRST(), 0) END
              


              All I did was changed <> 12 to <= 12.


              Yes MAX() is being used to aggregate the Month of Week field. But since you have the Month of Month of Week field in the viz, you could just as easily use MIN() as this will return the same result. This is because the Month of Month of Week is only going to return a single value. [ SUM() is the aggregate you need to stay away from as this would add up all the month parts for all the records.]  You could also use ATTR() to do the aggregation if this is less confusing for you.


              There's probably an LOD expression we could use to get rid of that WINDOW_SUM table calculation, but you didn't mention what version of Tableau you are using. LODs are only available in 9.x


              --Shawn





              1 of 1 people found this helpful
              • 4. Re: how to pass a running total to a detail row?
                Celia Yan

                Hi Shawn,

                 

                Your reply is really helpful!!

                 

                My Tableau is 9.0. Thanks for letting me know the new LOD feature. I downloaded the whitepapers and learned its powerful functions. However, I get a more challenging task based on the above question and am stuck on only one thing with LOD. Hope you can help me with this:

                 

                Now, the revenue is counted on the Deal level. For each deal, the total revenue count should show the minimum b/w total Contract revenue and total Delivered revenue (meaning, we don't charge the total over-delivered part). But on a monthly basis we only recognize contract revenue and reconcile revenue numbers only for the last month of a deal. Compared to the above question, the cum difference b/t contract and delivered still needs to be added to the last month for each deal but based on one condition -  whether the difference b/t SUM (Contract) and SUM(Delivered) is negative.

                 

                Here are two sample under-delivered deals (if over-delivered, it's easy to handle, just using contract revenue and ignoring the difference):

                Sample Data 2.png

                 

                In the table, the Adjusted Revenue marked in Red is what I'm looking for. For deal D0453, we want to recognize total delivered revenue as 458,950,118. Since the first three months recognized contract revenues, the last month needed to reconcile the number by adding the total difference to June's contract count.

                 

                In Tableau, I created a formula as below. It didn't work because I figured that under deal there could be more than one lines carrying contract revenue counts. So if a deal has more than one line, the below query marked in Yellow means adding the total difference count to each line.

                Sample Formula.png

                 

                I can use custom SQL to sum all lines up first and then import it into Tableau. But curious if there is a way to directly fix this but keep all fields. Please advise. I have attached the Tableau workbook here.

                 

                Much thanks,

                Celia

                • 5. Re: how to pass a running total to a detail row?
                  Shawn Wallwork

                  Thanks for the workbook! Celia I won't get to this until this weekend, so hopefully someone can help you out before then.

                   

                  Cheers,

                   

                  --Shawn

                  • 6. Re: how to pass a running total to a detail row?
                    Celia Yan

                    Shawn,

                     

                    Thanks for letting me know. No rush. This weekend will be good. If you still couldn't get to this then, I will create a new post and hopefully someone else can help me out.

                     

                    Much thanks!:-)

                     

                    Celia