Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Any input will be appreciated.
Thank you
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!