Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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
120
230
360
40

 

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
Gysbert_Wassenaar

The table you posted contains no information about customers.


talk is cheap, supply exceeds demand
Not applicable
Author

   

Period
CusNumDays Due
1A20
2B30
2A60
2B0
3A60
4B
Not applicable
Author

My apologies - really not getting this right today.

   

Period
CusNumDays Due
1A20
1B30
2A60
2B0
3A60
3B
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand
Not applicable
Author

Is there a way to catergorise this in one colomn?

jagan
Luminary Alumni
Luminary Alumni

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
Master III
Master III

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;

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.