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.
1 of 1 people found this helpful
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!
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!
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.