22 Replies Latest reply: Feb 14, 2017 7:29 AM by Stefan Wühl

How to show repeat purchase..

Hi Everyone,

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

Its really very very urgent....

Regards,

Kavita

• Re: How to show repeat purchase..

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

_tempTable:

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

Month(InvoiceDate) as InvMonth,

Year(InvoiceDate) as InvYear;

...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:

*,

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

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

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

• Re: How to show repeat purchase..

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

Regards,

Kavita

• Re: How to show repeat purchase..

Its really very very urgent.

• Re: How to show repeat purchase..

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:
AutoNumberHash128(  [DMS Outlet Code], MonthCounter) as Key;
MonthName(MakeDate(InvYear, InvMonth)) as InvMonthName,
Year(InvoiceDate)*12+ Month(InvoiceDate) as MonthCounter
Resident UCMaster;

DROP TABLE UCMaster;

TMP:
Key,
MonthCounter,
[DMS Outlet Code]
RESIDENT
NewMaster;

Result:
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..

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

Thank You so much for ur reply...

• Re: How to show repeat purchase..

Ok, if you need an or logic:

Result:

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

Thank You for ur precious time and response..

• Re: How to show repeat purchase..

Here is another ( IMHO more flexible)  approach:

Result:

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

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

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

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

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

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

• Re: How to show repeat purchase..

swuehl

stalwar1

gwassenaar

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

Hi,

• Re: How to show repeat purchase..

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

• Re: How to show repeat purchase..

and i have attached the data also..

Kinldy check the qvw.

Its very urgent for me to do this by EOD.

• Re: How to show repeat purchase..

Search the forum for NEW, LOST, RETURNING customers, it seems you want to show something like this.

There are multiple approaches, if you want to compare fixed periods, you can also try set analysis: For example, to show outlets that had no sale in max month but sale in max month -2, something like

=Count(

{<[DMS Outlet Code] =

e({<InvMonth = {"=InvMonth=\$(=Max({1}InvMonth))"}>})*p({<InvMonth = {"=InvMonth=\$(=Max({1}InvMonth))-2"}>} )

>}

DISTINCT [DMS Outlet Code])

• Re: How to show repeat purchase..

Thanks a lott..Stefan..

Really thankful to you..for all the solutions...

Regards,

Kavita

• Re: How to show repeat purchase..

The first one is correct...

Thanks Again..