Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
ecabanas
Contributor

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

Re: How to do this??

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
Contributor

Re: How to do this??

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

This widget could not be displayed.
This widget could not be displayed.
This widget could not be displayed.
This widget could not be displayed.
This widget could not be displayed.
This widget could not be displayed.
This widget could not be displayed.