Qlik experts help needed...!!!

[#1]

For the given table individual users with multiple credit cards and their respective payments are recorded. Now we need to calculate the total payments of those rolling two months followed by consecutive rolling 3 months or more of their missed payments. That means after they missed their payments for a while in a row and whenever they start making the payment the rolling two months payment amount to be calculated. There is a chance for this event to happen n number of times for the same combination of user and credit card. Attached is a sample data file and next tab shows the expected output. I am not sure if I am missing anything? or is it the one next to impossible? Any help is greatly appreciated.

Thank you

Suraj

[MK_QSL · Accepted solution]

Use below Script...

Temp:

Load *, User_Name & ' | ' & CreditCard as Key, If(Payment = 0, 1,0) as PaymentFlag;

LOAD

  User_Name,

    CreditCard,

    Date#(FiscalYrMonth,'YYYYMM') as FiscalYrMonth,

    Payment

FROM

[146446.xls]

(biff, embedded labels, table is Sheet1$);

A:

Load

  Key,

  User_Name,

  CreditCard,

  FiscalYrMonth,

  Payment,

  PaymentFlag,

  If(Key = Previous(Key) and PaymentFlag = 1, RangeSum(Peek('PaymentFlagTotal'),PaymentFlag),PaymentFlag) as PaymentFlagTotal

Resident Temp

Order By Key, FiscalYrMonth;

Drop Table Temp;

B:

Load

  Key,

  User_Name,

  CreditCard,

  FiscalYrMonth,

  Payment,

  PaymentFlag,

  PaymentFlagTotal,

  If(Key = Previous(Key) and Payment > 0 and Previous(PaymentFlagTotal) >= 3, 1,0) as Flag

Resident A

Order By Key, FiscalYrMonth;

Drop Table A;

C:

Load

  *,

  If(Key = Previous(Key) and (Flag = 1 or Previous(Flag) = 1), 1, 0) as NewFlag

Resident B

Order By Key, FiscalYrMonth;

Drop Table B;

Drop Fields Key, PaymentFlag, PaymentFlagTotal, Flag;

===============================================================

Now create a Straight Table

Dimension

User_Name

CreditCard

SUM({<NewFlag = {1}>}Payment)

===============================================================

Hope this would help

[Anonymous]

Hi,

Further to Manish solution,please replace below syntax with:

SUM({<NewFlag = {'1'}>}Payment)

with:

aggr(SUM({<NewFlag = {'1'}>}Payment),User_Name,CreditCard)

I hope its what you are looking for

Regards

Neetha

[#4]

Exactly what I was looking for. But a small question, what if I have to sum the succeeding 12 month payments?

Thank you

Suraj

[MK_QSL]

Would try for you.. Let me know exactly what you are looking for with an example in excel file..

[#6]

Attached excel is the sample data for 10000 odd rows. It has Person ID and his Credit Card ID. When it comes to the exact requirement if the customer missed consecutive 24 months or more of their missed payments and immediately started paying their debts wanted to calculate how much of their paid in the rolling 12 months since they started their payments. Same req as above but instead of 3 missed payment and 2 consecutive month payments. It is 24 months of consecutive missed payments and rolling 12 months of payments since a person started his payments for individual credit card.

Thank you

Suraj

[MK_QSL]

Can you limited your data and give me an output required?

[#8]

Hey, attached is the excel file with 'sheet 2' consisting of sample data and Output.

Thank you

Suraj

[MK_QSL]

Use below script...

Temp:

LOAD

  PersonID & '|' & CreditCardID as Key,

  PersonID,

    CreditCardID,

    Date#(Month,'YYYYMM') as Month,

    Payment,

    If(Payment = 0, 1, 0) as PaymentFlag

FROM

[146446 Suraj.xls]

(biff, embedded labels, table is Sheet2$);

A:

Load

  Key,

  PersonID,

  CreditCardID,

  Month,

  Payment,

  PaymentFlag,

  If(RowNo() = 1, 1, If(Key = Previous(Key) and Payment = 0, RangeSum(Peek('TotalPaymentFlag'),PaymentFlag),1)) as TotalPaymentFlag

Resident Temp

Order By Key, Month;

Drop Table Temp;

B:

Load

  *,

  If(Key = Previous(Key) and Peek('TotalPaymentFlag')<=11, RangeSum(Peek('NewFlag'),1),0) as NewFlag

Resident A

Order By Key, Month;

Drop Table A;

C:

Load

  *,

  If(Key <> Previous(Key), 0,

  If(Payment > 0 and NewFlag = 0, Peek('Case')+1, Peek('Case'))) as Case,

  Year(Month) as Year,

  If(NewFlag <=11, RangeSum(Peek('TP'),Payment), 0) as TP

Resident B

Order By Key, Month;

Drop Table B;

Drop Fields Key, PaymentFlag, TotalPaymentFlag, NewFlag;

============================

Now Create a straight table

Dimensions

1)

PersonID

2)

CreditCardID

3) Calculated Dimension

If(Case <> 0, Case)

Tick Suppress When Value is Null

Expression

1)

Max(TP)

2)

Min({<TP = {'>0'}>}Year) & '-' & Max({<TP = {'>0'}>}Year)

Hope this would help