Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
nicouek
Creator
Creator

Set Analysis for Aggregation

Hello all,

I need help finding a solution for counting  "Order ID" where "Order Date" occurs in one QUARTER, and "ship Date" happen in other QUARTER.  (Other than using filters)

eg: below

nicouek_0-1688909894223.png

 Any input will be appreciated.

Thank you

Labels (1)
1 Reply
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi there,

This is a good example of using Advanced Search in Set Analysis, or as they call it now in the Help documentation,  the "expression search". Since you are looking for selecting Orders, your filter should be based on the [Order ID] field, and the advanced search condition should require that the [Order Date]'s quarter is different than the [Ship Date]' quarter.  So, if you are using a "regular" Gregorian calendar, your Set Analysis condition could look like this:

[Order ID] = {"=ceil(Month([Order Date])/3) <> ceil(Month([Ship Date])/3)"}

If your quarters are calculated based on a fiscal calendar, then you should replace each "ceil" function with the specific quarter calculation.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!