Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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
MVP & Luminary
MVP & Luminary

Re: Calculation

See attached example.


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
MVP & Luminary
MVP & Luminary

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
MVP & Luminary
MVP & Luminary

Re: Calculation

See attached example.


talk is cheap, supply exceeds demand

View solution in original post

Not applicable

Re: Calculation

Is there a way to catergorise this in one colomn?

MVP & Luminary
MVP & Luminary

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 & Luminary
MVP & Luminary

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.