Apply calculation on running total and display in a crosstab

[bforsman]

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

[DellSC]

What is the exact formula for "{@netMRCCNR}"? I have some thoughts about how to do this, but I need to know what this formula is first. Thanks!

[bforsman]

Thank you for looking at it! The formula is
'if isnull({task.actualend}) and year({salesorder.new_confirmedduedate}) = year(currentdate) and month({salesorder.new_confirmedduedate}) < month(currentdate) then {salesorder.new_netmrc}'

[abhilash_kumar · Accepted solution]

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

[bforsman · Accepted solution]

This is FANTASTIC!

Thank you so much!