1

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.

Cross tab & Detail table screenshot Count of ACT ID element [RunningSum element3

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.

Subtotal element

Suggestions?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
ABrown78
  • 29
  • 6
  • `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` so show us smth you did, now it's only words – Suncatcher Nov 03 '20 at 09:40
  • Updated with screens. – ABrown78 Nov 03 '20 at 19:26

1 Answers1

1

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

enter image description here

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

enter image description here

Isaac
  • 3,240
  • 2
  • 24
  • 31