[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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!
0
whitfa
Asked:
whitfa
  • 2
  • 2
1 Solution
 
mlmccCommented:
Can you sum using a summary function?

Running totals are evaluated in the last pass of building a report (known as the printing records pass).  Charts are built before that pass thus the data is not available.

Try Right click the field you want to summarize
CLick INSERT --> SUMMARY

If that doesn't work here is a method for using printtime formulas in charts
http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_charting_print_time_formulas.pdf.asp

mlmcc
0
 
whitfaAuthor Commented:
Hi, thanks for your reply.

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.



0
 
mlmccCommented:
You are correct that you can only nest subreports 1 deep.

DOn't know off hand.

mlmcc
0
 
whitfaAuthor Commented:
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 ;-)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now