4 Replies Latest reply on Sep 5, 2013 2:24 PM by Damian Marquith

    Aggregation on Dimension Details within Table Calculations

    Damian Marquith

      Good morning Tableau guru's,

       

      In a bit of a kerfuffle for the past few days, I need to be able to quantify the number of customer status changes over a set range of time.  In my example, I have a hierarchy for CUSTOMER, with division, client, customerName, and customerStatus.  customerStatus can be either "PS" or "NP" for participating or non-participating.  The data set is aggregated over a fiscal month that spans 6 months.

       

      I am using Table Calculations to determine based on the previous month, if there was a status change, and I am using the following formula,

       

      if LOOKUP(attr([customerStatus]),-1)="NP"

         AND (attr([customerStatus])="PS")

      THEN "ADD"

       

      elseif LOOKUP(attr([customerStatus]),-1)="PS"

         AND (attr([customerStatus])="NP")

      THEN "DROP"

       

      ELSE "NO CHANGE"

      END

       

      (hat tip to Jonathan Drummey - very helpful comments in these forums and personal blog http://drawingwithnumbers.artisart.org/


      So, the now frustration.. I just want to know how many ADD's, DROP's, and NO CHANGE's have taken place for each period.  I have tried Computed SETS, but cannot use Table Calcs in the Set definition formula.


      This may be linked to a few open ideas, so if I am rehashing what everyone else know's isn't available at the moment, at least I would have an answer.. "you can't get there form here."


      I am also considering querying my data source to create a column that will have this change identified.. however, for this I would constrain my end users to use whatever time period I script.. I would prefer it to be dynamic.


      Any help appreciated.


      Damian

        • 1. Re: Aggregation on Dimension Details within Table Calculations
          Shawn Wallwork

          Try:

           

          ADDS

           

          if LOOKUP(attr([customerStatus]),-1)="NP"

             AND (attr([customerStatus])="PS")

          THEN 1 END

           

           

          DROPS

           

          if LOOKUP(attr([customerStatus]),-1)="PS"

             AND (attr([customerStatus])="NP")

          THEN 1 END

           

           

          NO CHANGE

          if LOOKUP(attr([customerStatus]),-1)="NP"

             AND (attr([customerStatus])="PS")

          THEN 0

           

          elseif LOOKUP(attr([customerStatus]),-1)="PS"

             AND (attr([customerStatus])="NP")

          THEN 0

           

          ELSE 1

          END

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: Aggregation on Dimension Details within Table Calculations
            Damian Marquith

            Thanks Shawn,

             

            I had something similar already... but when I try to aggregate the calculated fields (ADDS, DROPS, and NO CHANGE), I end up with the following:

             

            Aggregation on Dimension Details within Table Calculations-SAMPLE-1.PNG.png

             

            ... already tried to aggregate on the three calculated fields.. and I ended up getting the "can't aggregate on an aggregate"

            • 3. Re: Aggregation on Dimension Details within Table Calculations
              Wilson Po

              Sounds like you were simply about 1 layer away from getting there. 

               

              All that was needed once we saw something like that was to aggregate the counts 1 step further to get the total number of ADDs, DROPs, and NO CHANGES.  By using a cohort calc within a WINDOW_SUM, we can figure out the # of customer for each period based on that criteria: 

               

              WINDOW_SUM(IF [STATUS CHANGE]="ADD" THEN 1 END)

               

              We repeat this for each status change and address this new calc based on the CustomerName Level.  This allows for Status to still be evaluated based on the Fiscal Month, but then we add up the customers within the period based on the criteria laid out for each formula.  Finally, turning on the "Stacked Marks" will cut down the noise with each customer repeating the total value, and instead layer the totals for each customer in the same area. 

               

              While we could take this one step further and limit the returns for totals to avoid overlaps, its probably not worth the extra effort.  Any time you are partitioning across a table calculation (as oppose to a dimension found in the data), it will get somewhat difficult and intensive.   

               

              Hope this helps!

              • 4. Re: Aggregation on Dimension Details within Table Calculations
                Damian Marquith

                Perfect!!  Thank you very much.