4 Replies Latest reply: Jun 15, 2017 7:48 AM by Sunny Talwar

# Running sum based on a changing field value

Hello all,

I would like to calculate the running sum over several values depending on a changing variable (for example: whether it is a main version or not).

Here is an example. The wished result is in bold:

VersionMain versionDev costTotal for main version
1Yes25k25k
2No5k0
3No10k0
4Yes12k27k
5No11k0
6Yes30k41k

How can I reach this result please?

Nicolas

• ###### Re: Running sum based on a changing field value

Is this needed in the script? and what is the logic behind going from 25 to 27? 25 + 12 = 37? How are you getting 27?

• ###### Re: Running sum based on a changing field value

It can be done in the Load script or in a formula.

Actually, it calculate the total dev cost of each main version, including the cost of the previous non-main version.

We have 27k because we have 5k(v2) +10k(v3)+12k(v4-main version => sub total)

• ###### Re: Running sum based on a changing field value

Try this.

Data:

A, B, C, D

1, Yes, 25, 25k

2, No, 5, 0

3, No, 10, 0

4, Yes, 12, 27k

5, No, 11, 0

6, Yes, 30, 41k

];

Data1:

NoConcatenate Load * Resident Data order by A Asc;

Drop table Data;

Data2:

Resident Data1;

Drop table Data1;

Data3:

Resident Data2;

Drop table Data2;

Regards,

Kaushik Solanki

• ###### Re: Running sum based on a changing field value

May be this

Table:

Version, Main version, Dev cost

1, Yes, 25

2, No, 5

3, No, 10

4, Yes, 12

5, No, 11

6, Yes, 30

];

TempTable:

If([Main version] = 'Yes', RangeSum(Peek('Temp'), 1), Peek('Temp')) as Temp,

If([Main version] = 'Yes', RangeSum(Peek('Temp'), 1), Peek('Temp') + 1) as Temp1

Resident Table

Order By Version;

FinalTable: