topic Re: Create a flag to show the orders placed by the same customer for the same product within 8 days of their first order in App Development

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

Qlikhat — Fri, 15 Nov 2024 21:22:00 GMT

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.

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

Cascader — Tue, 03 Oct 2023 09:51:11 GMT

might this solve your issue:

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

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

Or — Tue, 03 Oct 2023 09:52:46 GMT

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