Calculating Cumulative Sum with Duplicate Values

[siva0606]

Hi All,

I have the below data set to achieve the output and below is the example.

INPUT:

ID                      DATE                        CODE                VALUE

127400            11/22/2019           A12                    1.5

127400            11/22/2019           A13                    1.5

127400            12/24/2019           A12                    2.5

127400            12/24/2019           A13                    2.5

127400            12/30/2019           A12                    3.5

127400            12/30/2019           A13                    3.5

OUTPUT:

ID                      DATE                      CODE             VALUE           CUMULATIVE_VALUE

127400            11/22/2019         A12                 1.5                   1.5 (Just VALUE)

127400            11/22/2019         A13                 1.5                   1.5 (Just VALUE)

127400            12/24/2019         A12                 2.5                   4.0 (VALUE+peek(CUMULATIVE_VALUE)

127400            12/24/2019         A13                 2.5                   4.0 (VALUE+peek(CUMULATIVE_VALUE)

127400            12/30/2019         A12                 3.5                   7.5 (VALUE+peek(CUMULATIVE_VALUE)

127400            12/30/2019         A13                 3.5                   7.5 (VALUE+peek(CUMULATIVE_VALUE)

Kindly help me on achieving the CUMULATIVE_VALUE column .

Best Regards,

Siva

[Anil_Babu_Samineni]

Perhaps this?

RangeSum(Above(TOTAL Aggr(Sum(distinct VALUE), VALUE), 0, RowNo(TOTAL)))

[siva0606]

Hi Anil,

Thanks much for your reply.

I forgot to mention one thing. I need the calculation to be done on script level.

Best Regards,

Siva

[Saravanan_Desingh]

One Solution is.

tab1:
LOAD *, If(ID=Peek(ID) And VALUE <> Peek(VALUE), RangeSum(VALUE,Peek(CUMULATIVE_VALUE)), Alt(Peek(CUMULATIVE_VALUE),VALUE)) As CUMULATIVE_VALUE;
LOAD RecNo() As RowID, * INLINE [
ID, DATE, CODE, VALUE
127400, 11/22/2019, A12, 1.5
127400, 11/22/2019, A13, 1.5
127400, 12/24/2019, A12, 2.5
127400, 12/24/2019, A13, 2.5
127400, 12/30/2019, A12, 3.5
127400, 12/30/2019, A13, 3.5
];

[Saravanan_Desingh]

Output.

[siva0606]

Hi Saran,

Thank you very much for your quick response.

and the logic is working as expected if we have one Id . If the other id comes , then facing the below issue in cumulative column.

I have shared the screenshot below.

In RowIdD =7, the cumulative value should be 1.5 and not 7.5.  Kindly check and let me know.

Thanks much in advance.

Best Regards,
Siva

[Saravanan_Desingh · Accepted solution]

Please check this.

tab1:
LOAD *, If(ID=Peek(ID), If(VALUE <> Peek(VALUE), RangeSum(VALUE,Peek(CUMULATIVE_VALUE)), Alt(Peek(CUMULATIVE_VALUE),VALUE)),VALUE) As CUMULATIVE_VALUE;
LOAD RecNo() As RowID, * INLINE [
ID, DATE, CODE, VALUE
127400, 11/22/2019, A12, 1.5
127400, 11/22/2019, A13, 1.5
127400, 12/24/2019, A12, 2.5
127400, 12/24/2019, A13, 2.5
127400, 12/30/2019, A12, 3.5
127400, 12/30/2019, A13, 3.5
273400, 11/22/2019, A12, 1.5
];

[Saravanan_Desingh]

Output.

[siva0606]

Thank you so much and I have implemented the logic and it is working as expected.

Best Regards,

Siva