Cumulative sum in Load Script

[nathan4988]

Hi All,

Hopefully an interesting one.

I need to calculate the number of months a line has a negative incurred, but cumulatively.

An example of the data would be:

ID Month Incurred

1 Jan-18 1000

2 Jan-18 500

3 Jan-18 -20

4 Jan-18 35

5 Jan-18 60

6 Jan-18 -300

1 Feb-18 1500

3 Feb-18 -20

4 Feb-18 -65

5 Feb-18 1500

6 Feb-18 -300

1 Mar-18 1500

3 Mar-18 -20

4 Mar-18 -65

5 Mar-18 1500

6 Mar-18 100

1 Apr-18 -100

2 Apr-18 -150

5 Apr-18 1550

6 Apr-18 -30

So I want a formula that would work out the number of months the ID has had a negative Incurred as below:

ID Month Incurred Months Negative

1 Jan-18 1000 0

2 Jan-18 500 0

3 Jan-18 -20 1

4 Jan-18 35 0

5 Jan-18 60 0

6 Jan-18 -300 1

1 Feb-18 1500 0

3 Feb-18 -20 2

4 Feb-18 -65 1

5 Feb-18 1500 0

6 Feb-18 -300 2

1 Mar-18 1500 0

3 Mar-18 -20 3

4 Mar-18 -65 2

5 Mar-18 1500 0

6 Mar-18 100 0

1 Apr-18 -100 1

2 Apr-18 -150 1

5 Apr-18 1550 0

6 Apr-18 -30 1

This is what my script looks like so far for the table.

[Negative Incurred Agg]:

Load

[Line ID], //Links to main data table

[Line ID] as [Neg Incurred Line ID], //Allows field to be used when not linked to main table.

[Neg Transaction Month],

[Negative Incurred],

[Negative Incurred Last 12 Months],

[Negative Incurred Last 18 Months],

IF ([Negative Incurred]<'0','1','0') as [Negative Incurred Count]

;

Load

[NegIncLineID] as [Line ID],

[TrMonth] as [Neg Transaction Month],

lastvalue([NegIncurred]) as [Negative Incurred],

If([TrMonth] > Date(MonthStart('$(vCalendarThisMonth)', -12)) and [TrMonth] <= Date('$(vCalendarThisMonth)'), 1,0) as [Negative Incurred Last 12 Months],

If([TrMonth] > Date(MonthStart('$(vCalendarThisMonth)', -18)) and [TrMonth] <= Date('$(vCalendarThisMonth)'), 1,0) as [Negative Incurred Last 18 Months]

Group by [NegIncLineID],[TrMonth]

;

Load

[NegIncLineID],

date(monthend([TrMonth]),'MMM YYYY') as [TrMonth],

[NegIncurred]

;

Load

ID as [NegIncLineID],

TrDate as [TrMonth],

Incurred as [NegIncurred]

resident MissingDatesData;

I was thinking possibly a peek using the negative incurred count field?

[PrashantSangle]

try below script

Test:

Load *,

Index(Incurred,'-') as count

Inline[

ID,Month,Incurred

1,Jan-18,1000

2,Jan-18,500

3,Jan-18,-20

4,Jan-18,35

5,Jan-18,60

6,Jan-18,-300

1,Feb-18,1500

3,Feb-18,-20

4,Feb-18,-65

5,Feb-18,1500

6,Feb-18,-300

1,Mar-18,1500

3,Mar-18,-20

4,Mar-18,-65

5,Mar-18,1500

6,Mar-18,100

1,Apr-18,-100

2,Apr-18,-150

5,Apr-18,1550

6,Apr-18,-30

];

Final:

Load *,

     if(ID=Previous(ID),if(count=1,RangeSum(count,Peek('cummlative_cnt')),count),count) as cummlative_cnt

Resident Test

Order by ID,Month;

DROP table Test;

EXIT SCRIPT;

Regards,

[Lesny_Eva · Accepted solution]

try this:

TempTable:

Load ID,

Incurred,

     Month,

if (Incurred < 0, 1, 0) as FlagIncurred //every negative Incurred gets a Flag; we use this to sum them up

;

Load ID, Month, Incurred

Inline [

ID,Month,Incurred

1,Jan-18,1000

2,Jan-18,500

3,Jan-18,-20

4,Jan-18,35

5,Jan-18,60

6,Jan-18,-300

1,Feb-18,1500

3,Feb-18,-20

4,Feb-18,-65

5,Feb-18,1500

6,Feb-18,-300

1,Mrz-18,1500

3,Mrz-18,-20

4,Mrz-18,-65

5,Mrz-18,1500

6,Mrz-18,100

1,Apr-18,-100

2,Apr-18,-150

5,Apr-18,1550

6,Apr-18,-30

6,Apr-18,-40

];

//because we need to sort the table we need to reload the data in a new table

Table:

LOAD

ID,

Incurred,

     Month,

     if(peek(ID)=ID, if(not Peek(Month) = Month, peek(runningIncurred) + FlagIncurred, peek(runningIncurred)),FlagIncurred) as runningIncurred, //this counts every month only once

     if(peek(ID)=ID, peek(runningIncurred) + FlagIncurred, FlagIncurred) as runningIncurredMultiple //this counts month multiple times    

Resident TempTable

order by ID //, Month /* you have to sort by month, but it has to be a date field. */

;

Drop Table TempTable;

Exit Script;

[nathan4988]

Thank you. This worked really well. I had to tweak it slightly as I wanted it to reset back to zero when it was no longer negative.

So I added in:

     if(peek(ID)=ID and Incurred<=0, peek(runningIncurred) + FlagIncurred, FlagIncurred) as runningIncurredMultiple //this counts month multiple times