Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlikhat
Contributor
Contributor

Create a flag to show the orders placed by the same customer for the same product within 8 days of their first order

I am new to Qlik Sense.  I need to add a flag to flag the orders where the same customer has ordered the same product within 8 days from the previous order. I tried using previous in the script like 

 

if(previous("Customer ID")="Customer ID" and previous("Product ID") = "Product ID" and "Order Date"-previous("Order Date") <=8,1,0) as DuplicateFlag

which doesn't seem to work. What I intend to accomplish is that the flag should loop through the dates column to check if any two days fall within the interval of 8 days.

Tried using fabs(interval(date#("Order Date", DD/MM/YYYY)-(previous(date#("Order Date",DD/MM/YYYY))), 'd')) as nDays which also did not work.

Any help would be really appreciated.

Thanks in advance. 

Labels (1)
2 Replies
Cascader
Creator
Creator

 

might this solve your issue:

aggr(Count(Distinct If(order date <= Today()-8 ,ProductID)), Customer ID) as OrderIDCountPerCustomer
Or
MVP
MVP

Is your data sorted by Customer, Product, and Order Date ascending (in that order)? That would be necessary for the Previous() approach to work.