Set analysis & Alternate states | Set anti-joins / Set differences

[TheP]

It's my first question here so if I'm posting that in incorrect place, please let me know.

I have Headcount data for my organization that I load from QVD. QVD includes following fields for sake of simplicity:

[Employee ID CODED]
[Report Period]
[Region]
[Country]

I wanted to create an app to show HC changes between selected Report Periods and use Set Analysis for sake of quick reload and minimal data manipulation in script (all our previous apps rely heavily on load scripts but I figured that for this specific example it should be possible to use Set Analysis).

I have created following Alternate States:

[Previous Period]
[Current Period]
[Overall]

[Previous Period] is used with Filter Pane with field [Report Period], similar with [Current Period].
[Overall] state is used with larger Filter Pane with fields [Region], [Country] etc.

I want to get metrics on data changes including following:

New Joiners - records where [Employee ID CODED] exists in [Current Period] state but not in [Previous Period]
Leavers - records where [Employee ID CODED] exists in [Previous Period] state but not in [Current Period]

I tried to make a measure for Leavers in following fashion:

Count({([Current Period] - [Previous Period]) * [Overall]} [Employee ID CODED])

This however does not work properly. Instead it shows same value as following measure:

Count({[Current Period]} [Employee ID CODED])

 After checking many materials regarding Set Analysis I still don't see what I'm doing wrong hence would appreciate any support on how can I achieve that avoiding creating flags in script (as this is something I already know how to do).

Currently, my organization runs QS for Windows 2023 Aug Patch 6, though we are moving towards Qlik Cloud (already have it but now it works by copying apps over from QS and we do all dev in QS still).

[Oleg_Troyansky]

Hi there,

First, allow me to voice a concern - this solution will only fly with a small data set. If your company employs large numbers of people, this approach could add a lot of unnecessary overhead.

Having said that, if the data set is small enough, let's try to make this solution work.

While Set Analysis syntax allows some Boolean operations with Set Identifiers, I'm not 100% sure how elaborate can it get - with parentheses etc... So, I'd look at possibly making this condition more specific with the use of the function P(). I'd formulate it this way:

Count({<
[Employee ID CODED]= (P({[Current Period]}) - 
P(<[Previous Period]>)) 
* P({[Overall]})
>} [Employee ID CODED])

Assuming that the rest of the conditions align correctly (the use of alternate states on objects, the correct set of selections, etc...), I believe this condition should produce the desired result.

Cheers,

[TheP]

Thanks Oleg,

While your solution did not work (expression syntax error), it prompted me to try a bit with those P() and E() functions and I came up with following (which seems to work for now):

Leavers:

-Count({<[Employee ID CODED] = P({[Previous Period]}) - P({[Current Period]})> * [Overall] * [Previous Period]} [Employee ID CODED])

Explanation: minus as I show those values in waterfall; [Employee ID CODED] = Possible within [Previous Period] - Possible within [Current Period]. That gets me all IDs that were in [Previous Period] but are not in [Current Period]. I later have to combine this selection with [Previous Period] to limit records to only that snapshot and not count same IDs in all older periods. I also combine with [Overall] to get all other filters.

Joiners:

Count({<[Employee ID CODED] = P({[Current Period]}) - P({[Previous Period]})> * [Overall] * [Current Period]} [Employee ID CODED])

Explanation is along same lines as above but other way around.

Thanks again and I hope this will be useful for someone 😄

I still need to take Transfer In/Out, Going on leave and returning into account but that's another story that will probably be possible by including more P() conditions.