How to show repeat purchase..

[kavita25]

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

[Gysbert_Wassenaar]

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])

[swuehl]

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

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

[Gysbert_Wassenaar]

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

[kavita25]

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

[kavita25]

Please help me with this isuee...

Its really very very urgent.

[swuehl]

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])

[kavita25]

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

[kavita25]

Thank You so much for ur reply...

[swuehl]

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  

  ;