whitfa
asked on
Charting a running total, and the difference between the total records and this running total.
Hi, I'm struggling with this one.
I'm wanting to chart cumulatively, the behaviour of my customers over time. So they either Exit, Upgrade, or Do Nothing. I want to end up with a 100% stacked chart of this behaviour week by week. For each record I am using a 1 or 0 for each @Exit and the same for @Upgrade. There is a date only for these two behaviours, which I am charting.
I'm using the sum of these fields in running totals; #Exit and #Upgrade, and failing miserably at calculating out the @Do Nothing's as Total Records - (#Exit + #Upgrade).
The chart won't let me include @Do Nothing, presumably because the Total Records has to be calculated at the end of the print. I've tried doing this using variables in the folumalae, but then these formulae don't appear in my chart data options. I've tried the Evaluate After options, but no cigar.
I've a feelng I'm making this overly complicated. Any help gratefully received!
I'm wanting to chart cumulatively, the behaviour of my customers over time. So they either Exit, Upgrade, or Do Nothing. I want to end up with a 100% stacked chart of this behaviour week by week. For each record I am using a 1 or 0 for each @Exit and the same for @Upgrade. There is a date only for these two behaviours, which I am charting.
I'm using the sum of these fields in running totals; #Exit and #Upgrade, and failing miserably at calculating out the @Do Nothing's as Total Records - (#Exit + #Upgrade).
The chart won't let me include @Do Nothing, presumably because the Total Records has to be calculated at the end of the print. I've tried doing this using variables in the folumalae, but then these formulae don't appear in my chart data options. I've tried the Evaluate After options, but no cigar.
I've a feelng I'm making this overly complicated. Any help gratefully received!
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
You are correct that you can only nest subreports 1 deep.
DOn't know off hand.
mlmcc
DOn't know off hand.
mlmcc
ASKER
Hi, I managed to get around this one by labeling every "Do Nothing" as occuring @ week = -10, despite them not having a date in the records. This way, I could bunch all my Do Nothings at the start of the running total, and chart it along with running totals of the Exit and Upgrades.
I did however find your workaround useful @mlmcc and employed it elsewhere in my main report. I'll award you the points - not that you need them mate ;-)
I did however find your workaround useful @mlmcc and employed it elsewhere in my main report. I'll award you the points - not that you need them mate ;-)
ASKER
I don't think I can use either option. I can't use the print time workaround as this report is already a sub report*, and I don't think I can nest them that far.
My chart should show week by week, the behaviour as described above. The weeks are calculated as a datediff between a fixed date, and the Exit or Upgrade date, so show from week = -10 to week +10. The Do Nothing people don't have a behaviour date attached to them, and this is why I can't chart them because they don't subtotal in any given week.
I can chart the running total of Exits and Upgrades (and stack them), but I can't add in the remainder of the population (Do Nothings) to make a 100% stacked chart.
*I tried doing this week by week cumulative behaviour in the main report, but because of other Grouping levels the series appears to spike and trough and spike again, when it should be an increasing line since it's cumulative. I'm not sure that I can therefore pass an ordered week by week list to the subreport/ chart suggested in the workaround.