topic How to do this?? in QlikView

How to do this??

ecabanas — Fri, 27 Sep 2013 15:35:23 GMT

Hi,

I would like to cluster my customers as a "Superloyal" if a Customer bought at minimum once order every month last 3 months.

I have the following table and I would like that the script fill the field "Superloyal" and I have no idea how to do it...

Thank's

Eduard

CUSTACCOUNT_CONTADOR	DATEFINANCIAL_CONTADOR	SuperLoyal
C000002	31/08/2012	No
C000002	31/10/2012	No
C000002	31/12/2012	No
C000002	31/01/2013	No
C000002	28/02/2013	Yes
C000002	31/03/2013	Yes
C000002	30/04/2013	Yes
C000002	31/05/2013	Yes
C000002	31/07/2013	No
C000002	31/08/2013	No
C000002	30/09/2013	Yes
C000014	31/05/2012	No
C000014	31/07/2012	No
C000014	31/08/2012	No
C000014	30/09/2012	Yes
C000014	31/10/2012	Yes
C000014	30/11/2012	Yes
C000014	31/01/2013	No
C000014	28/02/2013	No
C000014	31/03/2013	Yes
C000014	30/04/2013	Yes
C000014	31/05/2013	Yes
C000014	31/07/2013	No
C000014	31/08/2013	No

Re: How to do this??

Fri, 27 Sep 2013 18:59:21 GMT

If you want to create this field inside the load you can do it like this, but you will loose your date flexibility:

LET vMonths=addmonths(today(),-3); //This will create the begin date "Today -3 months".

TableName:
LOAD

CUSTACCOUNT_CONTADOR,

DATEFINANCIAL_CONTADOR

if(DATEFINANCIAL_CONTADOR>=$(vMonths),'Yes','No') as SuperLoyal

FROM TableName.qvd;

//Allways when you gonna use function inside a load, declare it inside a variable, that way qlikview don't //will be running the function row by row.

You can do it at the app.

Create a var

=if(GetSelectedCount(DateField)>=1,only(DateField),today())

And you will need to create a Calculated Dimension.

if(DATEFINANCIAL_CONTADOR>=$(varAbove),'Yes','No') as SuperLoyal

Re: How to do this??

manas_bn — Fri, 27 Sep 2013 19:37:31 GMT

Hi Eduard,

I think this is a similar question you had asked earlier - http://community.qlik.com/thread/93970

You can try the below:

//Raw Data

TEMP_Fact:

LOAD * // Create Month Field

,MonthName(date) as Month

INLINE [

Salesid, custaccount, date

1, A, 30/06/2013

2, B, 30/07/2013

3, A, 15/07/2013

4, B, 31/08/2013

5, A, 15/09/2013

6, B, 24/04/2013

7, A, 30/07/2013

8, C, 23/03/2013

9, C, 25/06/2013

10, C, 23/07/2013

11, C, 23/08/2013

];

//// Calculate No of Months and Sales in last 90 days

LEFT JOIN (TEMP_Fact)

LOAD

custaccount

,count(distinct Month) as NoOfMonths

,count(custaccount) as [Sales (last 90 days)]

RESIDENT TEMP_Fact

//Last 90 days only

Where num(date)>=num(today())-90

group BY custaccount ;

Fact:

NoConcatenate

Load Salesid, custaccount, date, Month,

// Sales for the last 90 days

if((num(date)>=num(today())-90),[Sales (last 90 days)],0) as [Sales (last 90 days)],

if(NoOfMonths>=3,'Super Loyal Customer',null()) as LoyalCustomerFlag

Resident TEMP_Fact;

drop table TEMP_Fact;

Result:

Re: How to do this??

Clever_Anjos — Fri, 27 Sep 2013 20:36:29 GMT

Hi Eduard,

Check if this app solve your requirements.

I´ve implemented a logic into set analysis, because a customer can be superloyal this month and not next month, am I Right?

Re: How to do this??

ecabanas — Mon, 30 Sep 2013 06:40:20 GMT

Hi Clever,

We are very very close, but the calculations are not doing well what we want. To be a Superloyal the customer has to be bought at least once PER month, if you buy 2 orders last month, but the month-3=cero, you are not superloyal...if you buy one month, one mont-2 and one month-3 you are a superloyal 😉

And.....has to move with the month calendar....very very trcicky code

Thank you very very very much Clever

Regards from Barcelona

Re: How to do this??

Clever_Anjos — Mon, 30 Sep 2013 13:55:09 GMT

Well,

Im aggregation per DATEFINANCIAL_CONTADOR, so more than one order, should count as one.

Which combination of filters are not doing well?

Re: How to do this??

Anonymous — Mon, 30 Sep 2013 15:21:35 GMT

Hi,

Try this:

FACT_tmp:
LOAD

*,
CUSTACCOUNT_CONTADOR&DATEFINANCIAL_CONTADOR as %KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR

;
LOAD * INLINE [
CUSTACCOUNT_CONTADOR, DATEFINANCIAL_CONTADOR
C000002, 31/08/2012
C000002, 31/10/2012
C000002, 31/12/2012
C000002, 31/01/2013
C000002, 28/02/2013
C000002, 31/03/2013
C000002, 30/04/2013
C000002, 31/05/2013
C000002, 31/07/2013
C000002, 31/08/2013
C000002, 30/09/2013
C000014, 31/05/2012
C000014, 31/07/2012
C000014, 31/08/2012
C000014, 30/09/2012
C000014, 31/10/2012
C000014, 30/11/2012
C000014, 31/01/2013
C000014, 28/02/2013
C000014, 31/03/2013
C000014, 30/04/2013
C000014, 31/05/2013
C000014, 31/07/2013
C000014, 31/08/2013
];

JOIN

LOAD

CUSTACCOUNT_CONTADOR,
DATEFINANCIAL_CONTADOR,
MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2)) as DATEFINANCIAL_CONTADOR2,
'1' as Flag

RESIDENT

FACT_tmp
Where
Exists (%KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR,CUSTACCOUNT_CONTADOR&MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2)))
;

JOIN

LOAD
CUSTACCOUNT_CONTADOR,
DATEFINANCIAL_CONTADOR,
MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1)) as DATEFINANCIAL_CONTADOR3,
'1' as Flag2

RESIDENT

FACT_tmp
Where

Exists (%KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR,CUSTACCOUNT_CONTADOR&MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1)))
;

FACT:
LOAD

CUSTACCOUNT_CONTADOR,
DATEFINANCIAL_CONTADOR,
if(Flag+Flag2=2,'SuperLoyal') as SuperLoyal

RESIDENT

FACT_tmp;

DROP TABLE FACT_tmp;

It should do exactly what you need.

Re: How to do this??

Mon, 30 Sep 2013 15:46:39 GMT

Eduard:

Try this attached model.

The above script is made to compare just the last 5 dates (starting from this month), however, it is very easy to change it if you need more to compare. You would just need to:

a) Change the corresponding "TO value" in both FOR clauses

b) Add as many new columns as needed in the CROSSTABLE (next one would be f_status(DAY4, DAY5, DAY6) as '[UPTO $(v_day6)]', etc)

Let me know if this helps.

RTapia

Re: Re: How to do this??

ecabanas — Tue, 01 Oct 2013 09:00:57 GMT

I tryed your code but when I uploaded some new data the calculations did not goes well, see the project attached and please, could you take a look if something is wrong

Thank you very much

Eduard

Re: Re: How to do this??

ecabanas — Tue, 01 Oct 2013 09:04:43 GMT

Hi Ruben

The problem is not to look for the sales accum, the calculation is:

present month has bought? Yes

present month -1 has bought? Yes

present month -2 has bought? Yes then super loyal

if somebody has bough 2 last month but not the mont-2 is not a superloyal

Thank you very much for your help...this is a very tricky problem.

Eduard

Re: How to do this??

Anonymous — Tue, 01 Oct 2013 09:57:49 GMT

Hi,

I thought that all your dates where month end date but you have all kind of dates, that is why it didn't work.

Try this, it works fine:

FACT_tmp:

LOAD

*,
CUSTACCOUNT_CONTADOR&month(DATEFINANCIAL_CONTADOR)&year(DATEFINANCIAL_CONTADOR) as %KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR;
LOAD * INLINE [
CUSTACCOUNT_CONTADOR, DATEFINANCIAL_CONTADOR
C000014,29/09/2011
C000014,17/11/2011
C000014,20/11/2011
C000014,22/01/2012
C000014,01/04/2012
C000014,11/05/2012
C000014,17/05/2012
C000014,10/07/2012
C000014,21/08/2012
C000014,22/09/2012
C000014,26/10/2012
C000014,17/11/2012
C000014,08/01/2013
C000014,28/02/2013
C000014,18/03/2013
C000014,01/04/2013
C000014,10/04/2013
C000014,12/05/2013
C000014,20/05/2013
C000014,23/05/2013
C000014,01/07/2013
C000014,27/08/2013
C000002,17/10/2011
C000002,28/12/2011
C000002,28/12/2011
C000002,02/08/2012
C000002,17/08/2012
C000002,26/10/2012
C000002,12/12/2012
C000002,22/12/2012
C000002,04/01/2013
C000002,25/01/2013
C000002,15/02/2013
C000002,13/03/2013
C000002,01/04/2013
C000002,19/04/2013
C000002,13/05/2013
C000002,30/05/2013
C000002,04/07/2013
C000002,13/08/2013
C000002,16/09/2013
C000014,29/09/2011

];

JOIN

LOAD

CUSTACCOUNT_CONTADOR,
DATEFINANCIAL_CONTADOR,
MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2)) as DATEFINANCIAL_CONTADOR2,
'1' as Flag

RESIDENT

FACT_tmp
Where
Exists (%KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR,CUSTACCOUNT_CONTADOR&Month(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2))&Year(AddMonths(Date(DATEFINANCIAL_CONTADOR),-2)))
;

JOIN

LOAD
CUSTACCOUNT_CONTADOR,
DATEFINANCIAL_CONTADOR,
MonthEnd(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1)) as DATEFINANCIAL_CONTADOR3,
'1' as Flag2

RESIDENT

FACT_tmp
Where

Exists (%KEY_CUSTACCOUNT_CONTADOR_DATEFINANCIAL_CONTADOR,CUSTACCOUNT_CONTADOR&Month(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1))&Year(AddMonths(Date(DATEFINANCIAL_CONTADOR),-1)))
;

FACT:
LOAD

CUSTACCOUNT_CONTADOR,
DATEFINANCIAL_CONTADOR,
if(Flag+Flag2=2,'SuperLoyal') as SuperLoyal

RESIDENT

FACT_tmp;

DROP TABLE FACT_tmp;

Re: How to do this??

Tue, 01 Oct 2013 12:13:15 GMT

Eduard:

I did not accumulate the sales. I simply mark each month with an "X" if there were sales or not.

Please check again, because based on your data, it's working as you expect, that is, for the last five dates, only C000002 is considered SuperLoyal in September (it's considered NOT superloyal in august for example, since in June there were no sales).

Awaiting your feedback,

Regards

Rtapia