topic Re: Qlik experts help needed...!!! in QlikView

Qlik experts help needed...!!!

Sun, 21 Dec 2014 06:44:03 GMT

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

Re: Qlik experts help needed...!!!

MK_QSL — Sun, 21 Dec 2014 13:12:02 GMT

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$);

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;

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;

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

Re: Qlik experts help needed...!!!

Anonymous — Sun, 21 Dec 2014 14:06:00 GMT

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

Re: Qlik experts help needed...!!!

Sun, 21 Dec 2014 18:17:01 GMT

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

Thank you

Suraj

Re: Qlik experts help needed...!!!

MK_QSL — Sun, 21 Dec 2014 18:21:30 GMT

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

Re: Qlik experts help needed...!!!

Mon, 22 Dec 2014 02:35:08 GMT

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

Re: Qlik experts help needed...!!!

MK_QSL — Mon, 22 Dec 2014 10:01:55 GMT

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

Re: Qlik experts help needed...!!!

Mon, 22 Dec 2014 19:47:22 GMT

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

Thank you

Suraj

Re: Qlik experts help needed...!!!

MK_QSL — Tue, 23 Dec 2014 17:59:24 GMT

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$);

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;

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;

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

PersonID

CreditCardID

3) Calculated Dimension

If(Case <> 0, Case)

Tick Suppress When Value is Null

Expression

Max(TP)

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

Hope this would help