Set analysis with date between two date fields

[lwestmaas]

Hello,

I had what I thought was a good idea that does not seem to be working at all.

I have a table of candidates linked to a table containing dates ("extract date"). Candidates have a "first appears" and "last appears" value, both dates. I am creating a line chart showing how many candidates were in the system on any given date. So, extract date is the dimension. I want my set analysis to include a candidate if and only if the (dimensional) extract date falls between the "first appears" and "last appears" date for the candidate.

I've tried the following:

Count(distinct {$<[Last Appears]={">=$(=Extract Date)"},[First Appears]={"<=$(=Extract Date)"}>} [Candidate Number])

• candidate line is straight across the whole way, no variation

Count(distinct {$<[Extract Date]={">=$(=First Appears)<=$(=Last Appears)"}>} [Candidate Number])

• same but now it's not reactive to selections on extract date

Any ideas? Thank you!

If relevant, here is the data structure. I have three tables relevant to the calculation:

Right-side table contains position types, business units, and dates (dates are non-unique), and is linked to middle table by a key of position type+subtype

Middle table contains position descriptions and is linked to left-side table by position type

Left-side table contains individual candidate info with a position type. Candidates can apply for multiple positions so candidates are not unique within the table and can be linked to different positions. (not linked by type-subtype because subtype can't be determined based on candidate)

[Anil_Babu_Samineni]

Perhaps this will reserve?
Count(distinct {$<[Last Appears]={"=[Last Appears]>=[Extract Date]"},[First Appears]={"=[First Appears]<=[Extract Date]"}>} [Candidate Number])

[lwestmaas]

Unfortunately that didn't work, appears there's an error in syntax that I can't identify. Tried placing dollar signs and parentheses in a few places but nothing made it display.

[lwestmaas]

Update - that did actually work but ONLY if a single extract date is selected. Otherwise it calculates to 0!

[jonvitale]

What I think is going on here is that this part of your set expression:

{">=$(=Extract Date)"},

Is actually trying to do this:

{">=$(=Only([Extract Date])"}

Where it is trying to compare the "First Appears" date to the only value of "Extract Date" that is possible.

When you actually select a value for "Extract Date" this "Only" expression works, because there really is only one value.

But, you're thinking, "in the chart there is only one value available, because the measure is being calculated at a particular dimensional value", right?

Well, according to this post, a set expression is evaluated only once per chart, not for every dimensional value. So, that "Only" expression comes up null.

https://community.qlik.com/t5/QlikView-Documents/Evaluating-quot-sets-quot-in-the-context-of-a-dimen...

You may be able to fool it with a complicated "Aggr", i.e., create a virtual table that creates a record for each employee per each date, but I'm not sure.

Why not just put your summary metrics in the same table as the "Extract Date". So you'd have for each extract date a # of candidates field?

[sunny_talwar]

How about this

Count(DISTINCT {$<[Candidate Number] = {"=[Last Appears] >= [Extract Date] and [First Appears] <= [Extract Date]"}>} [Candidate Number])

[jonvitale]

Pretty sure this won't work, [Last Appears] and [Extract Date] are on two different tables and have no implicit relationship. How would this expression "know" which Extract Date to compare [Last Appears] to?

[UrsulaWhite]

I was having similar issues and the following solution worked for me:

count({< [Extract Date]={">=$(First Appears)<=$(Last Appears)"}>}distinct [Candidate Number])

My variable contained date value.  It was not an expression.  Hope this helps.