Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do I Show IDs of customers having > 1 purchase within any 5 day period.

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

3 Replies
galax_allu
Specialist
Specialist

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

………………….

MarcoWedel

Hi,

maybe one solution might be:

QlikCommunity_Thread_238766_Pic1.JPG

QlikCommunity_Thread_238766_Pic2.JPG

QlikCommunity_Thread_238766_Pic3.JPG

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

johnw
MVP
MVP

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