topic Re: Running sum based on a changing field value in App Development

Running sum based on a changing field value

Thu, 15 Jun 2017 10:08:49 GMT

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:

Version	Main version	Dev cost	Total for main version
1	Yes	25k	25k
2	No	5k	0
3	No	10k	0
4	Yes	12k	27k
5	No	11k	0
6	Yes	30k	41k

How can I reach this result please?

Many thanks in advance,

Nicolas

Re: Running sum based on a changing field value

sunny_talwar — Thu, 15 Jun 2017 11:20:13 GMT

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

Thu, 15 Jun 2017 11:25:00 GMT

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

kaushiknsolanki — Thu, 15 Jun 2017 11:41:36 GMT

Try this.

Data:

Load * inline [

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:

NoConcatenate Load A,B,C,D,If(Peek(B)='No',peek(Value)+C,C) as Value

Resident Data1;

Drop table Data1;

Data3:

Load A,B,C,If(B='Yes',Value,0) as Value

Resident Data2;

Drop table Data2;

Regards,

Kaushik Solanki

Re: Running sum based on a changing field value

sunny_talwar — Thu, 15 Jun 2017 11:48:01 GMT

May be this

Table:

LOAD * INLINE [

Version, Main version, Dev cost

1, Yes, 25

2, No, 5

3, No, 10

4, Yes, 12

5, No, 11

6, Yes, 30

];

TempTable:

LOAD *,

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:

LOAD *,

If(Temp1 = Previous(Temp1), RangeSum(Peek('Total for main version Temp'), [Dev cost]), [Dev cost]) as [Total for main version Temp],

If([Main version] = 'Yes', If(Temp1 = Previous(Temp1), RangeSum(Peek('Total for main version Temp'), [Dev cost]), [Dev cost]), 0) as [Total for main version]

Resident TempTable;

DROP Tables Table, TempTable;

DROP Fields Temp, Temp1, [Total for main version Temp];