topic Qlik Conditional logic in App Development

Qlik Conditional logic

Shivam22 — Sat, 08 Nov 2025 01:59:16 GMT

Hi All,

Can you please help me understand how to resolve below

ID	Name	Date	Product
123	John	1/1/2023	TV
234	John	2/1/2024	TV
345	John	2/2/2024	TV
456	John	3/4/2024	TV
567	John	3/6/2024	Speaker

I have to create two columns in above table with two conditions

Condition 1 - If John buying TV after one year from initial purchase and the next purchase after one year from last purchase then its New purchase but if he changes Product like Speaker in the same Year then its New ,if not Existing - Always initial purchase is New

Condition 2 - If John buying TV or Speaker after one year from initial purchase and the next purchase after one year from last purchase then its New purchase if not Existing - Always initial purchase is New

Results Like below

ID	Name	Date	Product	Condition 1	Condition 2
123	John	1/1/2023	TV	New	New
234	John	2/1/2024	TV	New	New
345	John	2/2/2024	TV	Existing	Existing
456	John	3/4/2024	TV	New	New
567	John	3/6/2024	Speaker	New	Existing

Thanks in Advance!

Re: Qlik Conditional logic

HirisH_V7 — Sat, 08 Nov 2025 03:46:56 GMT

Hi look into this,

LOAD ID, Name, Date(Date#(Date, 'M/D/YYYY')) as Date, Product, If((Date - Previous(Date)) >= 365, 'New', 'Existing') as New/Existing, // Condition 1 If(RowNo()=1, 'New', If(Product <> Previous(Product) and Year(Date)=Year(Previous(Date)), 'New', If(Year(Date)-Year(Previous(Date)) >= 1, 'New', 'Existing')) ) as [Condition 1], // Condition 2 If(RowNo()=1, 'New', If(Year(Date)-Year(Previous(Date)) >= 1, 'New', 'Existing') ) as [Condition 2] Resident YourTable Order By Name, Date;

Re: Qlik Conditional logic

HirisH_V7 — Tue, 02 Dec 2025 08:19:40 GMT

Did you find a solution to your question? Mark the solution as accepted and if you found it useful. else Continue the thread.

Re: Qlik Conditional logic

mikekelvin — Tue, 02 Dec 2025 14:09:09 GMT

Check out the script

// 1. Load the Data and Ensure Correct Date Formatting TempData: LOAD ID, Name, Date(Date#(Date, 'M/D/YYYY')) as PurchaseDate, // Convert to Qlik Date Value Product INLINE [ ID, Name, Date, Product 123, John, 1/1/2023, TV 234, John, 2/1/2024, TV 345, John, 2/2/2024, TV 456, John, 3/4/2024, TV 567, John, 3/6/2024, Speaker ]; // Sort the data by Name and then PurchaseDate SortedData: NoConcatenate LOAD ID, Name, PurchaseDate, Product RESIDENT TempData ORDER BY Name, PurchaseDate ASC; // **CRUCIAL STEP** for sequential logic DROP TABLE TempData; --- // 2. Apply Sequential Logic using Peek() FinalData: LOAD ID, Name, Date(PurchaseDate) as Date, // Keep Date for display Product, // Get the previous Name, Date, and Product for comparison Peek('Name', -1, 'SortedData') as PrevName, Peek('PurchaseDate', -1, 'SortedData') as PrevDate, Peek('Product', -1, 'SortedData') as PrevProduct, // Check if this is the first purchase (GroupName check) // NOTE: Peek('Name', -1) will be null or different if this is the first record for a Name If(IsNull(Peek('Name', -1, 'SortedData')) OR Name <> Peek('Name', -1, 'SortedData'), 'New', // Calculate the difference: 1 year must have passed (>= 365 days) If( PurchaseDate >= AddYears(Peek('PurchaseDate', -1, 'SortedData'), 1), 'New', // Condition 1: Check for product change in the same year/less than a year // The purchase must be 'New' if the product changed AND the 1-year gap was NOT met. If( Product <> Peek('Product', -1, 'SortedData'), 'New', 'Existing') ) ) as [Condition 1], // Condition 2: Simpler logic - only checks for the 1-year gap (Always 'New' initially) If(IsNull(Peek('Name', -1, 'SortedData')) OR Name <> Peek('Name', -1, 'SortedData'), 'New', If( PurchaseDate >= AddYears(Peek('PurchaseDate', -1, 'SortedData'), 1), 'New', 'Existing' ) ) as [Condition 2] RESIDENT SortedData ORDER BY Name, PurchaseDate ASC; // Maintain the sort order DROP TABLE SortedData;

@HirisH_V7 ,

Some of the issues that has been resolved includes:

1. The If(IsNull(Peek('Name', -1, 'SortedData')) OR Name <> Peek('Name', -1, 'SortedData'), 'New', ...) block ensures that the first purchase for any customer is correctly labeled "New," preventing errors if you add more names later.

2. Using PurchaseDate >= AddYears(PrevDate, 1) correctly enforces the requirement that the purchase date must be on or after the date one year later, accurately representing "after one year from last purchase."

3. The product change check is nested correctly. It's only evaluated if the 1-year gap was NOT met, perfectly matching the rule: "but if he changes Product like Speaker in the same Year then its New, if not Existing."

Hope this answer suits your question.