Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Calculation

See attached example.


talk is cheap, supply exceeds demand
9 Replies

Re: Calculation

The table you posted contains no information about customers.


talk is cheap, supply exceeds demand
Not applicable

Re: Calculation

   

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

Re: Calculation

My apologies - really not getting this right today.

   

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

Re: Calculation

See attached example.


talk is cheap, supply exceeds demand
Not applicable

Re: Calculation

Is there a way to catergorise this in one colomn?

MVP
MVP

Re: Calculation

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
Honored Contributor III

Re: Calculation

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

Re: Calculation

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

MVP
MVP

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.

Community Browser