I have formulas that calculate the monthly MRC on orders that have not been completed.We want to accumulate each running total and display in a crosstab to show how much revenue we are missing out on for orders that do not get completed.
This is an excel table that shows my desired results:
My formula for each month is similar to
'if {@month} = 1 then {@netMRCCNR}'
Where {@netMRCCNR} is the MRC on an order not yet completed.
I have a running total for each month and then tried adding them as follows:
'if {@month} = 1 then {#RTcnr01} else if {@month} = 2 then {#RTcnr02} + {@cnr01}*2 else if {@month} = 3 then {#RTcnr03} + {@cnr02}*2 + {@cnr01}*3
else if {@month} = 4 then {#RTcnr04} + {@cnr03}*2 + {@cnr02}*3 + {@cnr01}*4 else if {@month} = 5 then {#RTcnr05} + {@cnr04}*2 + {@cnr03}*3 + {@cnr02}*4 + {@cnr01}*5
else if {@month} = 6 then {#RTcnr06} + {@cnr05}*2 + {@cnr04}*3 + {@cnr03}*4 + {@cnr02}*5 + {@cnr01}*6
else if {@month} = 7 then {#RTcnr07} + {@cnr06}*2 + {@cnr05}*3 + {@cnr04}*4 + {@cnr03}*5 + {@cnr02}*6 + {@cnr01}*7.....'
This isn't working, I assume because of the IF statement for each month. But if I try to use a running total, it doesn't work because a running total cannot refer to a print time formula.
I'm at a loss how to accomplish this.
Thank you in advance
Hi Barbara,
If you're using a crosstab, you'd first need to insert an "Embedded summary" for the calculated summary.
1. Right-click one of the summary cells > Select Embedded Summary > Insert Embedded summary.
2. Right-click one of the cells labelled "Edit this formula" > Embedded summary > Edit calculation formula > type this code:
local numbervar cindex := CurrentRowIndex; local numbervar cval := tonumber(GridValueAt(CurrentRowIndex, CurrentColumnIndex, 0)); local numbervar fval := tonumber(GridValueAt(1, CurrentColumnIndex, 0)); local numbervar summ; local numbervar i; local numbervar j := cindex; local numbervar k := 1; IF CurrentRowIndex = 1 then fval Else ( For i := 2 to CurrentRowIndex do ( k := k + 1; j := j - 1; summ := summ + tonumber((GridValueAt(j, CurrentColumnIndex, 0) * k)); ); cval + summ; );
-Abhilash
Add a comment