Skip to Content
Jul 15, 2020 at 02:06 PM

Apply calculation on running total and display in a crosstab


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


capture.jpg (71.5 kB)