7 Replies Latest reply on Jul 23, 2013 8:11 AM by Jim Wahl

    Dynamic Dimension based on level of detail aggregation from another dimension

    Zoheb Hajiyani

      Hi all,

       

      I have a dataset that has client level revenue data based on the current format

       

      ClientQuarter2012 Rev2013 Rev
      AQ107
      BQ105
      AQ2100
      BQ2107

       

      I’m trying to create a dynamic dimension called Client Status that is one of the three following values:

      • Same Store Sales: If an client has rev in both 2012 and 2013
      • New Client: If an advertiser doesn’t have rev in 2012 but has rev in 2013
      • Churned Client: If an advertiser has rev in 2012 but no rev in 2013

       

      On my dashboard, I want to show the following view that has a breakdown of the dynamic dimension and revenue associated with each. I also want to have the functionality to filter on a specific quarter or a combination of quarters.

       

      Client Status2012 Rev2013 Rev
      Same Store Sales....
      New Client....
      Churned Client....

       

      Client Status should be based on the following logic:

      • Each Client should get categorized as a SS, New, or Churned based on the total revenue from all filters picked.
        • Therefore, if only Q1 is picked, client A gets classified as 'New' (as 2012 rev = 0, 2013 rev = 7), but if Q1 and Q2 are picked, client A gets classified as SS (as 2012 rev = 10, 2013 rev = 7)
      • After each advertiser get's assigned to a Client Status value, the above view should roll up the 2012 and 2013 rev values based on the aggregation of all clients that fall into each bucket.
        • Therefore, based on selecting Q1, Q2, or both, the following views would be shown:
          • When only Q1is selected: (Client A is classified as New, Client B is classified as New)
            Client Status2012 Rev2013 Rev
            New012
          • When only Q2is selected: (Client A is classified as Churned, Client B is classified as Same Store Sales)
            Client Status2012 Rev2013 Rev
            Churned100
            Same Store Sales107
          • When Q1 and Q2 are selected: (Client A is classified as Same Store Sales, Client B is classified as Same Store Sales)
            Client Status2012 Rev2013 Rev
            Same Store Sales2019

       

      Any help on this would be greatly appreciated. Attaching a sample data set for reference.

       

      Thanks!

      Zoheb

        • 1. Re: Dynamic Dimension based on level of detail aggregation from another dimension
          Jim Wahl

          Hi Zoheb,

           

          Like a lot of things in Tableau, you get 90% of the solution very quickly. But the final piece was tricky.

           

          To start with the easy part, I created a calculated field Client Status

          IF     SUM([2012 Rev]) == 0 and SUM([2013 Rev]) > 0  THEN "New Client"
          ELSEIF SUM([2012 Rev]) > 0  and SUM([2013 Rev]) == 0 THEN "Churned Client"
          ELSEIF SUM([2012 Rev]) > 0  and SUM([2013 Rev]) > 0  THEN "Same store sales"
          ELSEIF SUM([2012 Rev]) == 0 and SUM([2013 Rev]) == 0 THEN "No revenue"
          END
          

           

          Each SUM() is an aggregate over all data that's in the view at the level of detail in the view.

           

          The first part means that when you filter Q1, Q2, or Q3, Tableau applies the filter at the database level and the data never reaches the view or this function. For example, if you filter Q1, SUM([2012 Rev]) is summing all values in Q2 and Q3.

           

          The level of detail means the formula is calculated for each row in the view or for the level of detail specified in the detail shelf. Therefore, adding advertiser to the view or level of detail will compute the client status for each advertiser.

           

          Once you add this to the view, you get something very close to your goal:

          2013-07-18 14-21-33.png

          to be continued. ... (for some reason I can't post a longer reply). ...

          1 of 1 people found this helpful
          • 2. Re: Dynamic Dimension based on level of detail aggregation from another dimension
            Jim Wahl

            The problem is that advertiser is in the view, which is fine here, but probably won't work in general with 100s of advertisers; plus you want the sums (try turning on sub-totals). Once you move this from the rows shelf to the level of detail shelf, you may be in for a shock if you're new to Tableau.

            2013-07-18 15-23-35.png

            Tableau now is still performing the same calculation on the same data, but since advertiser is not in the view, there are multiple marks for each row. In Tableau V7, these would be on top of each other; in V8, they're "stacked" (you can revert to V7 by selecting Analysis > Stack Marks Off in top menu bar). The extra white space and the right-aligned column headers with left-aligned values always confuses me at first.

             

            What you want is a single value that is the sum of all values in the cell. My first thought was WINDOW_SUM(), but it doesn't work because client status is a measure (masquerading as a dimension) and you can't partition on it. Try adding window_sum() and you'll see the options are Table Across, Cell and Advertiser. Table across and Cell are equivalent to the non table calc version; advertiser sums the values for all advertisers across all statues.

             

            So I created two new table calcs: a feeder calc that sums the values in each cell and a value calc that selects the last value:

            2012 Rev SUM feeder =

            IF [Client Status] == LOOKUP([Client Status], -1)
            THEN ZN(SUM([2012 Rev])) + PREVIOUS_VALUE(0)
            ELSE ZN(SUM([2012 Rev]))
            END
            

             

            It's easiest to see how this is working by looking at the intermediate "result" worksheet, where client status and advertiser are both listed. For each row, this formula checks if the client status is the same as the previous row and if true, adds the current row's value to the previous row's value. This is kind of a manual partitioning.

             

            Next you want to select the last value from the above calc with the formula: 2012 Rev SUM value

            IF [Client Status] != LOOKUP([Client Status], 1) OR ISNULL(LOOKUP([Client Status], 1))
            THEN ZN([2012 Rev SUM feeder])
            END
            

             

            This formula also does manual partitioning. If the client status for the next row (LOOKUP(x, 1)) is different, we know the current value is the final value for the current client status group. To handle the last row, we look for a null next value.

             

            When you create these table calcs, it's a good idea to click on the "Default Table Calculation" text in the upper-right and in this case select "Advertiser".

             

            Add these two value calcs to the view (you don't need to add the feeder calcs, since they are "nested" inside the value calcs), and click on each and double check the compute using for all four (2x2) calcs is advertiser.

            • 3. Re: Dynamic Dimension based on level of detail aggregation from another dimension
              Jim Wahl

              Now the values should be correct. From the top menu bar, click Analysis > Stack Marks > Off to right-align the values. And now you can format to remove the white space.

               

              A couple of final cleanups to hide the labels for the client status header and edit the alias for the 2012 / 2013 column headers, and you've got what you want.

              1 of 1 people found this helpful
              • 5. Re: Dynamic Dimension based on level of detail aggregation from another dimension
                Jim Wahl

                Sorry about the multi-part message -- something funk was happening with the forms or at least with this message. ...

                 

                Jim

                1 of 1 people found this helpful
                • 6. Re: Dynamic Dimension based on level of detail aggregation from another dimension
                  Zoheb Hajiyani

                  Jim, thank you so much! As you mentioned, 90% of the solution was easy using the window sum, but I was having difficulties solving the problem as moving the 'Client' to the level of detail caused overlapping values. Also, thanks for the detailed explanation, really helped with understanding the details of solving this specific type of question.

                  • 7. Re: Dynamic Dimension based on level of detail aggregation from another dimension
                    Jim Wahl

                    Glad it worked --- thanks for the feedback.

                     

                    Jim