topic Re: How to show repeat purchase.. in QlikView

How to show repeat purchase..

kavita25 — Sat, 11 Feb 2017 17:47:09 GMT

Hi Everyone,

I want to show those customers who was repeated in next month..

stalwar1‌ gwassenaar‌

Please help me with this issue..

Its really very very urgent....

Regards,

Kavita

Re: How to show repeat purchase..

Gysbert_Wassenaar — Sat, 11 Feb 2017 18:03:01 GMT

Create a new table in your script from the first one and then drop that first table:

_tempTable:

load *,

Year(InvoiceDate)*12+ Month(InvoiceDate) as MonthCounter,

Month(InvoiceDate) as InvMonth,

Year(InvoiceDate) as InvYear;

LOAD

...some fields...

[DMS Outlet Code],

...some more fields...

Date(Date#(InvoiceDate,'DD MMM YYYY'),'DD/MM/YYYY') as InvoiceDate,

...rest of the fields...

FROM

[c249529.qvd]

(qvd)

;

Result:

LOAD

If(Previous(MonthCounter)=MonthCounter+1,1,0) as IsReturning

RESIDENT

_tempTable

ORDER BY

[DMS Outlet Code],

InvoiceDate desc

;

DROP TABLE _tempTable;

Then you can use this expression: Count({<IsReturning={1}>}DISTINCT [DMS Outlet Code])

Re: How to show repeat purchase..

swuehl — Sat, 11 Feb 2017 19:32:18 GMT

I think there is a factor missing in MonthCounter calculation to create unique and consecutive numbers:

Year(InvoiceDate) *12+ Month(InvoiceDate) as MonthCounter,

Re: How to show repeat purchase..

Gysbert_Wassenaar — Sun, 12 Feb 2017 07:02:20 GMT

You're completely right. Didn't copy it correctly from my workbook. Thanks!

Re: How to show repeat purchase..

kavita25 — Sun, 12 Feb 2017 08:56:45 GMT

Thank You Gysbert...

I just have a doubt, if i want to show same thing for next 3 months...

For an e.g :

There's as Sale for May, Jun and Sep..

So for the Month of May and Jun should be count of 1..but not for Sep..as its next any of the next three months doesn't have sale.

It means the month itself should have sale and in any of the next three month should have sale..

Kindly guide me...

gwassenaar‌ stalwar1‌ swuehl‌

Regards,

Kavita

Re: How to show repeat purchase..

kavita25 — Sun, 12 Feb 2017 12:31:29 GMT

Please help me with this isuee...

Its really very very urgent.

Re: How to show repeat purchase..

swuehl — Sun, 12 Feb 2017 13:22:18 GMT

It might be urgent for you, this does not imply that it's urgent for us, on a weekend ...

Maybe something like attached:


Binary 'Test (49).qvw';


NewMaster:
LOAD *, 
  AutoNumberHash128(  [DMS Outlet Code], MonthCounter) as Key;
LOAD *,
  MonthName(MakeDate(InvYear, InvMonth)) as InvMonthName,
  Year(InvoiceDate)*12+ Month(InvoiceDate) as MonthCounter
Resident UCMaster;


DROP TABLE UCMaster;

TMP:
LOAD DISTINCT 
  Key,
  MonthCounter,
  [DMS Outlet Code]
RESIDENT
  NewMaster;
  
Result:
LOAD  
  Key,
    If(Previous(MonthCounter)=MonthCounter+1 and  [DMS Outlet Code] = Previous( [DMS Outlet Code]),Rangesum(Peek(ReturningIn),1),0) as ReturningIn
RESIDENT TMP
ORDER BY
    [DMS Outlet Code],
    MonthCounter desc    
  ;

  DROP Table TMP;

 exit script;

Now you can filter the records to show only codes with at least N appearances in directly following months like

=Count({<ReturningIn ={">=3"}>}DISTINCT [DMS Outlet Code])

Re: How to show repeat purchase..

kavita25 — Sun, 12 Feb 2017 13:40:26 GMT

Hi,

Its showing only for Aug..it should show sale for that particular Month...and in next 3 month sale..

3 month sale is like it can be any month..

For.e.g Aug, Oct Dec..

Aug is the month,next 3 month sale is Sep Oct Nov...in oct it has sale..so it should be consider..

there's no compulsion that it sholud have sale for all the next 3 months...there will be OR condition..

Re: How to show repeat purchase..

kavita25 — Sun, 12 Feb 2017 13:42:48 GMT

Thank You so much for ur reply...

Re: How to show repeat purchase..

swuehl — Sun, 12 Feb 2017 16:24:10 GMT

Ok, if you need an or logic:

Result:

LOAD

Key,

If(Previous(MonthCounter) = MonthCounter+1 and [DMS Outlet Code] = Previous( [DMS Outlet Code]),1,0) as ReturningIn1,

If(Match( Previous(MonthCounter)- MonthCounter,1,2) and [DMS Outlet Code] = Previous([DMS Outlet Code]),1,0) as ReturningInAny2,

If(Match( Previous(MonthCounter)- MonthCounter,1,2,3) and [DMS Outlet Code] = Previous([DMS Outlet Code]),1,0) as ReturningInAny3

RESIDENT TMP

ORDER BY

[DMS Outlet Code],

MonthCounter desc

;

Re: How to show repeat purchase..

kavita25 — Sun, 12 Feb 2017 16:45:31 GMT

Thank You for ur precious time and response..

Re: How to show repeat purchase..

swuehl — Sun, 12 Feb 2017 16:51:28 GMT

Here is another ( IMHO more flexible) approach:

Result:

LOAD

Key,

If([DMS Outlet Code] = Previous( [DMS Outlet Code]),Previous(MonthCounter) - MonthCounter ) as ReturningAfter

RESIDENT TMP

ORDER BY

[DMS Outlet Code],

MonthCounter desc

;

Re: How to show repeat purchase..

kavita25 — Sun, 12 Feb 2017 17:09:25 GMT

Okay... i'll try this one also..

Can u please tell me..

How will I check the particular month has no sale but previous month has sale..

For e.g: Dec has no sale but Nov has sale..

And

Previous 2 month has sale but the previous 1 month and the particular month has no sale.

For e.g Oct has sale but (Dec and Nov) has no sale.

The only above logic is left to do..

Kindly guide me..

And thank you soo much...Really appreciate ur help..

Regards,

Kavita

Re: How to show repeat purchase..

kavita25 — Sun, 12 Feb 2017 17:13:22 GMT

The first one is correct...

Thanks Again..

Re: How to show repeat purchase..

swuehl — Sun, 12 Feb 2017 17:20:37 GMT

The first question can be answered just like the question that already has been answered before, you just need to change the sort order and add another field.

The second question could be answered using my second approach and filtering ReturningAfter >2 (or including maybe NULL).

Re: How to show repeat purchase..

kavita25 — Sun, 12 Feb 2017 17:35:42 GMT

For the first logic..

If(Previous(MonthCounter) = MonthCounter - 1 and [DMS Outlet Code] = Previous( [DMS Outlet Code]),1,0) as ReturningIn1

RESIDENT TMP

ORDER BY

[DMS Outlet Code],

MonthCounter asc

;

Is it correct??

Re: How to show repeat purchase..

swuehl — Sun, 12 Feb 2017 18:14:29 GMT

Yes, something like this. Just give it a try and test thoroughly.

Re: How to show repeat purchase..

kavita25 — Tue, 14 Feb 2017 10:41:02 GMT

swuehl‌

stalwar1‌

gwassenaar‌

Please help me with the issue...

How will I check the Max month has no sale but previous month has sale..

For e.g: In my data Max month is Dec has no sale but Nov has sale..

And

Previous to previous month has sale but Max month and previous month has no sale.

For e.g Oct has sale but (Dec and Nov) has no sale.

Re: How to show repeat purchase..

kavita25 — Tue, 14 Feb 2017 12:02:37 GMT

Hi,

please help me to do this logic

Re: How to show repeat purchase..

sunny_talwar — Tue, 14 Feb 2017 12:05:56 GMT

I am very late to this discussion and will have to check each and every response before I can answer anything. Also, you are getting help from two of the best here. I am going to let them continue helping out here