Intro from Rob: Um, why bother with an intro? Let’s proceed…
Olgeta! I’m Tim Siegenbeek van Heukelom (Twitter, LinkedIn, Blog) and work in the public international health field as program analyst for the Oil Search Health Foundation in Papua New Guinea (although most of the time I work from Sydney).
Part of my job is to create reports from the vast amounts of public health data the Health Foundation produces and/or keeps track of. For instance, I try to make sense of our HIV testing-, malaria diagnosis- and condom distribution data and measure it against performance indicators and targets.
In this post I will discuss one of the examples from Rob’s blog. Remember ‘Modelling Viral Growth’? This is an addendum, based on real-life application and modification of the model.
I recently ventured into using Power Pivot to model our condom distribution data against targets and baselines. At some point I got stuck, but Rob came to the rescue – and in the process found my tab called ‘Condoms Power’ particularly hilarious.
Merely a couple of months ago I was still a true novice at Power Pivot. Worse, at the start of this year I had not even heard of Power Pivot! In a short period of time I have come a long way, thanks in part to Rob’s enlightening book and the many examples on both his and Kaspar de Jonge’s blog.
As you may have suspected by now, I’ll be working here with condom distribution data (but the model is equally applicable to my HIV testing data).
After playing around with some standard pivot tables, I soon realised that Power Pivot was the tool of choice to help me plot the condom data against our annual growth target of a 20% increase in our distribution rates. However, I had to figure out how exactly to do this. Compounding the matter was that I was not sure if 20% was the most sensible growth rate as well as what I would take as our baseline.
But you have to start somewhere. So my first calculated measure simply took the previous quarter’s total number of condoms distributed and added a 20% increase to create the current quarter’s target. Of course I realised it did not make much sense to calculate a quarterly target based on last quarter’s actuals if in reality I need a 20% increase per annum.
Thus it soon became clear that 1) I needed a baseline, and 2) find a way to model annual growth. I quickly scanned Rob’s book again, but nothing stood out. Then it hit me; I remembered Rob’s three-part-post about modeling viral growth, could that help me out? Well, it certainly got me on the right track. I managed to create a manual baseline slicer as well as a percentage increase slicer – just like Rob had done in the viral growth workbook. But due to my inexperience with Power Pivot I eventually got stuck. The two key issues I struggled with were:
1. How to model a Continues Growth Factor (CGF) per quarter based on an annual growth rate, and have the growth continue into the following year?
2. How to create a slicer to pick a baseline from the actual data – not from manual entries on a disconnected table?
The Viral Growth Model had so far been extremely helpful, but to me it didn’t seem to offer a solution to these two issues. With a looming deadline to complete my data analysis for this year’s second quarter, I had to call in reinforcements troops. So I shot Rob a quick email, outlining the problem and hoping he could point me in the right direction.
To my surprise, within the hour I already had a response from Rob: he could certainly solve it (in exchange for this blog post – good deal if you ask me), and I’d made his day with my worksheet tab called ‘Condoms Power’. Another hour or so later I received my excel workbook back with a detailed explanation of how he’d solved my problems. Enough talk, let’s get into the nitty gritty of how it’s done.
1. Continuous Growth Factor (CGF)
Let’s start with the CGF. How to make a growth factor that continues quarter upon quarter, year upon year. The key problem here for me was that the CFG I had created myself started over every year.
I needed a way to step through time, quarter by quarter, and never have it reset.
Rob’s solution was ingenious: in the Power Pivot ‘DateMaster’ table he added a new column ‘QtrID’ that gave each quarter a unique ID. While some date tables (which are crucial to any decent Power Pivot) start at Q1/1900 to cover all date ranges, my ‘DateMaster’ starts in Q1/2000 since I have no data earlier than that. Hence, the ‘QrtID’ for Q1/2000 has the value ‘1’ and going forward to Q1/2012 we reach a QrtID of ‘49’. To do this we add a calculated column with the following formula:
The QtrID Column – Starts at 1 for Q1 2000, goes through 54 for Q2 2013
Now, to calculate the CGF you still use POWER(1+Growth Factor) as in the Viral Growth Model, but “raise it to the power of” MAX(DateMaster[QtrID])-[Min Selected QtrID])
MAX(DateMaster[QtrID])-[Min Selected QtrID]
[Min Selected QtrID] – ALLSELECTED to the rescue!
But what does [MIN selected QtrID] do? That is intended to detect the earliest selected quarter displayed on the pivot!
For instance, in this screenshot Q1/2012 (49) is the first quarter displayed, and that is the value displayed for all subsequent quarters in the pivot.
I have a row labels filter applied to the DateMaster[Year] column – only years 2012 and after are displayed. [Min Selected QtrID] “fetches” the lowest QtrID on the pivot!
[MIN selected QtrID]=
Note from Rob: this simply was not possible until we got the ALLSELECTED function in v2. All hail ALLSELECTED!
2. Slicer baseline from actual data
The second hurdle I encountered was to formulate a baseline (the starting value of the exponential series) based on my actual condom distribution data. It turned out that Rob’s solution was straight-forward: he created a new “disconnected slicer” Power Pivot table called ‘Qrt baseline slicer’ with a year; quarter, caption; and QrtID column.
A new measure called [SEL Qtr Baseline] was introduced to harvest the selection from the slicer and determine how many patients we had in the selected quarter:
SEL Qrt Baseline=CALCULATE(COUNTROWS(QryHIVRegister), ALL(DateMaster), FILTER(ALL(DateMaster), DateMaster[QtrID]=MAX(‘DateMaster’[QtrID])))
By now I had a CGF and a baseline, so calculating the Target is easy:
Target =[SEL Qtr Baseline]*[ContinousGrowthFactor]
The result is that I can filter my pivot table view (eg. select to display only 2012 and 2013), and then use two slicers two slicers to manually set the annual growth percentage and the baseline quarter. And, as noted before, the baseline quarter can be different from the first quarter displayed in the pivot table (as you can see below).
While this may be an interesting expansion/modification of Rob’s Viral Growth Model, I guess that the key takeaway from this post is that even the most unlikely Power Pivot examples can have a real-life application – and sometimes in very unexpected situations.
While this is certainly a good start, there is plenty of room for further thought and improvement of the model:
- - Is it sensible to take just the condom distribution of the last quarter of a year as baseline for the following year(s)? Would it be better to take a year’s quarterly average of condoms distributed to provide the baseline for the subsequent year(s)?
- - Should I pick one particular baseline (eg. Q4/2011) that I use to project quarterly targets for 2012 and years beyond, or should targets for 2012 be based on 2011 actuals and targets for 2013 be based on 2012 actuals? In other words, do I keep one baseline for multiple, subsequent years; or do I reset/review the baseline each year to stay in line with actual condom distribution data? If I were to opt for the latter, this means I can forego the baseline slicer and modify the ‘Target’ to automatically calculate quarterly targets for each year based on a set baseline of choice (and this baseline could simply be Q4 of the previous year, or anything else of my choice – as raised in first point of thought).
To conclude, I’m still working on refining the model and love to hear your thoughts and improvements.
Signing off from Port Moresby, Condoms Power FTW!