How to Generate a Burndown by Month
I have a dataset of action tracking records with due dates. I've been asked to generate a burndown curve, which I can do pretty simply in Excel, but I'm stumped on how to put this in a WebI report.
I created a variable to convert the due date for the records to yyyy-MM, so I can group them by the month they're due and count them. Then, I was able to add a runningsum of those counts in my crosstab.
[
The element that I'm missing is being able to refer to the total count of records (the total outside the column grouping of the crosstab) to subtract the running sum from.
Suggestions?
You need to deal with Calculation Contexts in order to do this. Here are a few good resources...
Removing the Confusion from Calculation Contexts
Calculation Context Part I: Overview
The More Things Change... (de facto Part II to Part I above)
To answer your question and show my work I created a free-hand SQL query using the SQL in this dbfiddle. Put into a crosstab with a running sum I get what you have in your example...
Next I created a variable called Var Report Total defined as...
=Sum([Act Count]) In Report
Then I created another variable called Var Remaining to calculate the difference...
=[Var Report Total] - [Var Running Sum]
And there you have it...
