3 Replies Latest reply on Dec 15, 2016 1:18 AM by Simon Runc

    Easy as Pie

    Shane Chatfield



      I've been struggling with creating a pie chart using calculated fields. What I'm trying to do is create a chart that shows the percent of participants who maintained or decreased their BMI and those who gained. Extra info about the data: Participants are measured every 6 weeks of treatment.

        • 1. Re: Easy as Pie
          Simon Runc

          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'



          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).

          1 of 1 people found this helpful
          • 2. Re: Easy as Pie
            Shane Chatfield

            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

              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 high-level 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.

              1 of 1 people found this helpful