Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How do I do the following: Show IDs of customers having > 1 purchase within any 5 day period. My table contains CUST_ID, PURCHASE_ID, SALE_DATE
=count({<SALE_DATE =
{">=$(=Date(Today()-6,'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))"}, {"count(PURCHASE_ID)>=1"}>} CUST_ID)
Or
=Only({<SALE_DATE =
{">=$(=Date(Today()-6,'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))"}, {"count(PURCHASE_ID)>=1"}>} CUST_ID)
………………….
Hi,
maybe one solution might be:
tabPurchase:
LOAD 'Sale'&RecNo() as PurchaseID,
'Cust'&Ceil(Rand()*10) as CustomerID,
DayName(Today()-Rand()*100) as PurchaseDate
AutoGenerate 30;
Join (tabPurchase)
LOAD PurchaseID,
If(CustomerID=Previous(CustomerID),PurchaseDate-Previous(PurchaseDate)) as DaysSinceLastPurchase
Resident tabPurchase
Order By CustomerID, PurchaseDate;
hope this helps
regards
Marco
=aggr(if(min(aggr(PurchaseDate - mid(concat(total <CustomerID> PurchaseID&':'&num(PurchaseDate),',',PurchaseDate),index(concat(total <CustomerID> PurchaseID&':'&num(PurchaseDate),',',PurchaseDate),PurchaseID&':'&num(PurchaseDate))-6,5),CustomerID,PurchaseDate,PurchaseID))<=5,CustomerID),CustomerID)
Haaahaha hahahahha hahahahah ahahahahaha <gasp> ahhahahahhaahhahahaha hahah <John runs away continuing to laugh hysterically. This expression clearly broke him. He's going to take a couple days off to recover. Please do not use this expression.>