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

    Running sum based on a changing field value

    Nicoals Martin

      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?

       

      Many thanks in advance,

       

      Nicolas

        • Re: Running sum based on a changing field value
          Sunny Talwar

          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
              Nicoals Martin

              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
                  Kaushik Solanki

                  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

                    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];