How to do this??

[ecabanas]

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

[#2]

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

[manas_bn]

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

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:

[Clever_Anjos]

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?

[ecabanas]

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

[Clever_Anjos]

Well,

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

Which combination of filters are not doing well?

[Anonymous]

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.

[#8]

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

[ecabanas]

Hi

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

[ecabanas]

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