Set Analysis for Aggregation

nicouek — Sun, 09 Jul 2023 13:41:21 GMT

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

Re: Set Analysis for Aggregation

Oleg_Troyansky — Sun, 09 Jul 2023 20:51:53 GMT

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!

topic Set Analysis for Aggregation in App Development

Set Analysis for Aggregation

Re: Set Analysis for Aggregation