Using Running Totals Within Formulas

[Kassam]

Afternoon All,

I have a report where I use Running Totals within Formula, But when I try to sum the Formula down to the groups it will not let me because I am using a running total with the formula.

Is there any way that I can put a running total within a formula and then sum it down?

What I am trying to do is:

{@Total Paid}
{@Aggregate}
{#Total Paid}

{@Total Paid} {@Aggregate} {#Total Paid} {@New}
100 600 100 100
200 600 300 300
100 600 400 400
200 600 600 600
400 600 1000 0
600 600 1600 0
700 600 2300 0

What I am trying to say within the {@New} Formula is:

If {#Total Paid} > {@Aggregate} Then 0 Else
{@Total Paid}

So If the Running Total Exceeds the Aggregate of 600 then put 0 else Total Paid Formula.


Then I wish to sum this down to the Groups.

I hope this makes sense.

Thank you very much for your assistance.

 

[lbass]

I think you could arrive at your group total without using the running total. When you say you wish to "sum this down to the groups," I assume you mean you want to carry the running total result > 0 ("600" in this example) down to the group level. I'm also assuming that {@aggregate} is a constant within each group, but might be different for different groups. If so, try this for your group total:

if sum({@totalpaid}, {table.group}) >= {@aggregate} then
{@aggregate} else sum({@totalpaid}, {table.group})

-LB

 

[Kassam]

Thanks for the suggestion, But I have tried this and it doesn’t give the desired result.

{@Total Paid} = {@Paid}+{@Reserve}


{@Paid} Formula

If {@Total Paid} > {@Aggregate} Then 0 Else {@Paid}

{@Reserve} Formula

If {@Total Paid} > {@Aggregate} Then 0 Else {@Reserve}


Then these 2 formula add up to {@Total Paid}

So {@Paid} could be 200 and {@Reserve} could be 400 when the {@Total Paid} = 600 The Aggregate Limit (i.e. {@Aggregate}

I hope this makes sense

Thank you once again for your assistance

 

[lbass]

Did you try my formula? I tested this and it works according to the requirements you specified. It should be placed at the group level (header or footer)--it is the total you want to achieve, not a detail level formula. You have to substitute your group field for {table.group} of course.

I don't know what you're trying to do with the formulas you just mentioned--they wouldn't work anyway, since formulas cannot refer to themselves.

Please try my suggestion and then if for some reason it doesn't work, show the kind of results you are getting so we can go from there.

-LB