3 Replies Latest reply on Feb 12, 2016 7:01 AM by Yuriy Fal

    Retaining WINDOW_SUM() results while removing partitions

    Kev M

      Hello, only a few weeks experience with tableau so hopefully there is an unknown elegant solution to my problem.

       

      Attached is a sheet with a sample of the problem I am trying to resolve.  The data use is supposed to represent a spend over time with fields holding Date, Client, DealID, Budget Type, Spend

       

       

      Details:

       

      Two clients exist:  Client 1 and Client 2

       

      These clients each have 2 different deals:  For client A Deal ID 1A and 1B and for client B Deal ID 2A and 2B

       

      There are two budget types: A and B.  With Budget Type A, the spend should never exceed the budget, that is SUM(Spent) > ATTR(Budget) then ATTR(Budget) should be shown as the total.  While in Budget Type B, the spend is allowed to exceed the budget, so SUM(Spent) should always be shown as the total, regardless of whether SUM(Spent) > ATTR(Budget)

       

      My first goal is to calculate the spend total following the rules of budget type.  This was easily achieved using the calculated field "Modified Spend" as is shown in the sheet "Calcluated Spend"

       

      What I would like to do now is SUM the results of modified spend, to show the total spend for both Client 1 and Client 2.  The straightforward approach to this is obviously wrong (see sheet "Incorrect sums"). The correct numbers can be achieved by using WINDOW_SUM([Modified Spend]) and compute using the client as the partition (Sheet "Window Sum" has some field hidden to retain partitioning), however this results in duplicate entries in the table, as it tries to write the WINDOW_SUM results in each parition, ideally the sums would only be shown once for the matching client.

       

      Is it possible to remove the duplicate WINDOW_SUM()s?  Is there a better way to approach this?

       

       

      Thanks!