Calculation

[#1]

Hi guys,

I have the following table and would like to aggregate the table as per the below:

Split by:
- State1- customers who have a days past due greater than the previous period
- State2 customers who have a days past due less than the previous period
- State3 - customers who moved from any arrears to current (0 Days)
- State4- customers who have a days past due that remains the same as the previous period

Period	Days Due
1	20
2	30
3	60
4	0

[Gysbert_Wassenaar]

The table you posted contains no information about customers.

[#3]

Period	CusNum	Days Due
1	A	20
2	B	30
2	A	60
2	B	0
3	A	60
4	B	0

[#4]

My apologies - really not getting this right today.

Period	CusNum	Days Due
1	A	20
1	B	30
2	A	60
2	B	0
3	A	60
3	B	0

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[#6]

Is there a way to catergorise this in one colomn?

[jagan]

Hi,

Try like this

Temp:

LOAD * INLINE [

    Period, CusNum, Days Due

    1, A, 20

    2, B, 30

    2, A, 60

    2, B, 0

    3, A, 60

    4, B, 0

];

Data:

LOAD *,

  If(CusNum=Previous(CusNum) and [Days Due] > Previous([Days Due]), 'State1',

  If(CusNum=Previous(CusNum) and [Days Due] < Previous([Days Due]), 'State2',

  If(CusNum=Previous(CusNum) and [Days Due] = 0, 'State3',

  If(CusNum=Previous(CusNum) and [Days Due] = Previous([Days Due]), 'State4')))) AS State

Resident Temp

Order By CusNum, Period

;

Drop Table Temp;

Regards,

Jagan.

[sasiparupudi1]

Like this?

T1:

LOAD * INLINE [

    Period, CusNum, Days Due

    1, A, 20

    1, B, 30

    2, A, 60

    2, B, 0

    3, A, 60

    4, B, 0

];

T2:

LOAD *,

    If(CusNum=Previous(CusNum) and [Days Due] > Previous([Days Due]), 1,

       If(CusNum=Previous(CusNum) and [Days Due] < Previous([Days Due]), 2,

           If(CusNum=Previous(CusNum) and Previous([Days Due])>0 and [Days Due] = 0, 3,

          If(CusNum=Previous(CusNum) and [Days Due] = Previous([Days Due] ), 4,0)))) as State

Resident T1

Order By CusNum, Period

;                       

Drop Table T1;

[#9]

Thank you so much... may I ask another question... how would this be done in an expression?

[jagan]

Hi,

In expression it is not feasible since the order of displaying the values may change, so do this in the script itself.

Regards,

Jagan.