
1. Re: Easy as Pie
Simon Runc Dec 13, 2016 2:27 PM (in response to Shane Chatfield)hi Shane,
So there are a few ways we could go about this, but the key is the formula to create the dimension gain/loss/no change/unknown (I've created an unknown class for patients who've only had their 1st measurement). I've also chosen to use FIXED LoDs as this means we can create the dimension as a "true" row level dimension, so we don't need to think about the vizLoD (this does however mean that the classification is created before any "regular" filters are applied, so if you want them to affect the classification you'll need to make them "in context")
So first I created the following calculation
[Last BMI per Patient]
{FIXED [Patient ID]: MAX(IIF(NOT(ISNULL([BMI])),[Visits Number],NULL))}
this brings me back the last valid Visit number for each patient
I also created a starting and end BMI field
[Starting BMI]
IIF([Visits Number] = 1,[BMI],NULL)
[Latest BMI]
IF [Visits Number] = [Last BMI per Patient] THEN [BMI] END
from here we can then create our dimension
[BMI Gain/Loss/Maintain/NA]
IF [Last BMI per Patient] = 1 THEN 'Unknown'
ELSEIF {FIXED [Patient ID]: MAX([Latest BMI])} > {FIXED [Patient ID]: MAX([Starting BMI])} THEN 'Gain'
ELSEIF {FIXED [Patient ID]: MAX([Latest BMI])} < {FIXED [Patient ID]: MAX([Starting BMI])} THEN 'Loss'
ELSE 'No Change'
END
and once we have this we can just use this to create our Pie Chart, using COUNTD of patient as the measure.
Hope this helps, and makes sense but let me know if not (I've also left in a Calc and Table Check sheet so you can see what each thing is doing).

BMI Pie Chart.twbx 134.5 KB


2. Re: Easy as Pie
Shane Chatfield Dec 14, 2016 11:20 AM (in response to Simon Runc)Simon  Thank you!
The only part I'm having trouble following is in the creation of the dimension. Why do you still need to use Max in the IF statements? Is it simply a part of using 'Fixed', which, I must admit, I don't entirely understand. Thank you, for any clarification you can offer!

3. Re: Easy as Pie
Simon Runc Dec 15, 2016 1:18 AM (in response to Shane Chatfield)hi Shane,
So FIXED LoD gives us the ability to run an aggregate calculation at a FIXED Level...and those results are returned at row level (so a full VizLoD independent dimension) at the level specified in the FIXED LoD. To help explain this I've put together an Excel for 1 Patient. In the below table, you can see how we use the Row Level calculations eg.[First BMI]: IIF([Visit Number] = 1, BMI, NULL), but these return the result to just that row...the FIXED LoD (using MAX) then takes that column of values, find the MAX and then returns that value to every row for that Patient ID (as this is the level specified in the FIXED LoD)
You might also find this article useful Answer  Quora where I've done a highlevel look at the calculation types and how this work.
So in short, we use the MAX to return the single row value to every row (for each Patient) so we can then compare them. This means (using the language of the above link) that the dimension we create is "Off Canvas". We could have done it without these but would have need both Patient ID and Visit Number in our VizLoD, which makes things pretty complicated.
Hopefully, that makes more sense, but let me know if not.

BMI Pie Explained.xlsx 9.0 KB
