Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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)
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
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];