4 Replies Latest reply on Jun 8, 2016 5:57 AM by Paul Gabb

    Dynamic Granular Aggregation

    Paul Gabb



      Hoping someone can help as i have asked all the Tableau greats i know at work...


      I am having issues creating a top line customer engagement tracker. On a monthly basis, i want to be able to count up the customers that have lapsed, reactivated, continuous, and new. But i only want to show the top line results. So i need to be able to calculate a Status based on a level of data more granular than i want to show. I have tried Fixed LOD calcs but these don't seem to work as give some incorrect values (it could be that i am setting these up wrong).


      Criteria is as follows:

      New - Join Date in that month and Spent > 0

      Lapsed - Spent in previous month but not in current month

      Reactivated - Didn't spend in previous month but has spent in current month

      Continuous - spent in both the previous and current month


      Desired result (using attached data)


      StatusJanuary 2016February 2016March 2016


      Any help would be greatly appreciated as it is driving me crazy!!





        • 1. Re: Dynamic Granular Aggregation
          Michel Caissie



          Because you need the lookup function to compute the  Lapsed,Reactivated,Continuous

          and because a LOD calc cannot contain a table calc, you need to have the accountNumber in the View.

          You can then compute those measures using a combination of  lookup and Window_Sum functions.

          Pay attention to the computing in each measure, because you have a table calc embedded in another table calc,

          and you have to set a different computing on each field.

          For example, isLapsed is Table Accross and  Lapsed is Table Down.


          Next, you can use LOD to compute the  New  without having to bring the Join Date in the view.


          Finally , you need a filter to keep a single row per Account.


          Check the sheet  Validate data first.  The final view is a duplicate, I just kept the 4 measures, applied the filter, and unselect Show Header on  Account Manager




          ps: if you have questions I wont be available until Monday.

          • 2. Re: Dynamic Granular Aggregation
            Carl Slifer

            Howdy Paul,


            There is an attached workbook, however the calculations are arguably 'advanced'. I will explain the gist of what I've done.


            I made a separate calculation for each of your measures. I've done this because your business logic requires table calculations. That is to say that in order to return information from month (t) and month (t-1) there needs to be a calculation that looks at the aggregated table and then looks back one partition (month in this case).  I do this for every single account ID.


            Then for each table calculation I built a specialized window calculation. In short a window calculation works after table calculations and will return a value for the entire visible window in every cell that it had looked across. In english. I was able to sum the already built table calculations and return the table total for every measure. When you use these you have to right click the measure and edit the table calculation. These are nesting table calcs inside one another so that the original calc must run across the table (so that it can see where t and (t-1) are in relation to each other) and then the window based calculation must run down the table so that it can sum and return a value for everything that it had looked at for the entire table.


            At this point we repeat the same information for all of the account IDs, so I used a filter and returned only the first row of the partition, with respect to table (down). This way everything was filtered off except one account ID.


            Next I unchecked the 'show header' option so that the end user has no clue that all account IDs exist.


            Finally I have hid December for 2 reasons, 1) because you did not show it in yours and 2) I cannot look up a previous month with December and I couldn't be asked to account for it being the first cell of a partition in my calculations in mock up data.


            Again- these are 'fairly advanced' but the concept should be doable. I would recommend reading up on table calculations in general. As I've used a few that most people do not readily know about and the nesting concept of them is passed an every day point and clicker needs.



            Hope this answers some of your questions and generates more!



            Carl Slifer


            1 of 1 people found this helpful
            • 3. Re: Dynamic Granular Aggregation
              Carl Slifer



              I purposely hunt down a 'hard' question from 12 hours previously so that I can play with it and give a long detailed answer and someone beats me by a few minutes. Well done!

              • 4. Re: Dynamic Granular Aggregation
                Paul Gabb

                Michel & Carl, huge thanks for your help guys. Apologies for the delay but only just managed to tinker around and make it work for real data. Seems to work well and reconcile to an acceptable level.

                My next challenge would be how could i use the resulting values as a % of total? i.e. total customers for the month = x. Reactivated = 20% of x etc etc. Viz would be a stacked graph but the result would enable analysis of more than just absolute values. I guess future developments would be pitching results against budgets etc which i imagine may be a stretch because of the structure needed just to get to this stage.


                Thanks again!