Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ecabanas
Creator II
Creator II

How to do this??

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_CONTADORDATEFINANCIAL_CONTADORSuperLoyal
C00000231/08/2012No
C00000231/10/2012No
C00000231/12/2012No
C00000231/01/2013No
C00000228/02/2013Yes
C00000231/03/2013Yes
C00000230/04/2013Yes
C00000231/05/2013Yes
C00000231/07/2013No
C00000231/08/2013No
C00000230/09/2013Yes
C00001431/05/2012No
C00001431/07/2012No
C00001431/08/2012No
C00001430/09/2012Yes
C00001431/10/2012Yes
C00001430/11/2012Yes
C00001431/01/2013No
C00001428/02/2013No
C00001431/03/2013Yes
C00001430/04/2013Yes
C00001431/05/2013Yes
C00001431/07/2013No
C00001431/08/2013No
11 Replies
Not applicable

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
Creator
Creator

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:

Capture.PNG.png

Clever_Anjos
Employee
Employee

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
Creator II
Creator II
Author

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
Employee
Employee

Well,

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

Which combination of filters are not doing well?

Anonymous
Not applicable

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.

Not applicable

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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