Can I make a Reverse Running Total? 2

[AZdesertdog]

I have a report that looks at a range of 24 months of totals and then does some calculations of individual columns depending on what month 'number' it is (1-24). This works well based on my running total that goes top to bottom 1-24 but sometimes I don't have data for all months and my report may be, say just months 1-13. This messes up my formulas because I really should be counting from the bottom up starting with '1'. I guess what I need is either a way to force all month totals (GF2) to display even if no data OR a reverse running total with 1 always being the last available month. Any ideas? Thanks! -JJP

Have...
#rtotal @charges @payments
1 $10 $5
2 $23 $3
3 $20 $5

Need...
#rtotal @charges @payments
3 $10 $5
2 $23 $3
1 $20 $5

 

[kenhamady]

If you want a running total that counts backwards from a fixed number or from a grand total you can create a normal running total and then use it in a formula. Write a formula that subtracts the RT from the fixed number or Grand total. Something like:

24 - {#RT}

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[AZdesertdog]

I see. The only problem with that is that I may have different numbers of totals than the desired 24. Here's an example of two different report results..

Only 8 months returning results..
#rtot1 (Desired formula)
1 8
2 7
3 6
4 5
5 4
6 3
7 2
8 1

The next report may have all 24 desired months of data..
#rtot1 (desired formula)
1 24
2 23
3 22
4 21
5 20
etc..

Basically- I want a formula where the last total is always number '1' no matter how many of the 24 desired totals are found. Does that help?

 

[lbass]

To account for variation in the number of months, you could use a running total that resets for each group, and then create a formula like Ken suggests, but use:

count({table.groupfield},{table.groupfield})+1 - {#runningtotal}

If you place this in the details section, it will start with the maximum number of months in the group and decrease by one with each month.

-LB

 

[AZdesertdog]

That's giving me some trouble. I have my details suppressed and the group footers are what I'm showing in the report. I have a running total as one of the fields in the footer. Isn't there some way I can find out what the largest number of my group footer is for that group and then subtract the #rtot0 from it for each summary? Seem easy but when I tried to create Max ({#rtot0}) I was told my rt couldn't be summarized.

Here's what I'm thinking- 3 formulas

#RTot0 - counts 1-whatever for each group summary (have this now)

@MaxRT find max running total number for this group (don't know how to do this)

@Reverse count
{@MaxRT}-{#RTot0}

 

[lbass]

If you are counting group footers, all you have to do is
create the running total to count a recurring field, evaluate on change of Group2 (month), reset on change of Group1. Place this in the group footer. Then change the count formula to something like:

distinctcount({table.Group2field}, {table.Group1field})+1 -{#runningtotal}

-LB

 

[AZdesertdog]

That worked! Thanks. -JJP