9 Replies Latest reply: Aug 4, 2015 4:20 AM by jagan mohan rao appala

Calculation

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

• Re: Calculation

The table you posted contains no information about customers.

• Re: Calculation

 Period CusNum Days Due 1 A 20 2 B 30 2 A 60 2 B 0 3 A 60 4 B 0
• Re: Calculation

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
• Re: Calculation

See attached example.

• Re: Calculation

Is there a way to catergorise this in one colomn?

• Re: Calculation

Hi,

Try like this

Temp:

Period, CusNum, Days Due

1, A, 20

2, B, 30

2, A, 60

2, B, 0

3, A, 60

4, B, 0

];

Data:

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.

• Re: Calculation

Like this?

T1:

Period, CusNum, Days Due

1, A, 20

1, B, 30

2, A, 60

2, B, 0

3, A, 60

4, B, 0

];

T2:

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;

• Re: Calculation

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

• Re: Calculation

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.