Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I want to show those customers who was repeated in next month..
Please help me with this issue..
Its really very very urgent....
Regards,
Kavita
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])
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])
I think there is a factor missing in MonthCounter calculation to create unique and consecutive numbers:
Year(InvoiceDate) *12+ Month(InvoiceDate) as MonthCounter,
You're completely right. Didn't copy it correctly from my workbook. Thanks!
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
Please help me with this isuee...
Its really very very urgent.
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])
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..
Thank You so much for ur reply...
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
;