Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lwestmaas
Contributor III
Contributor III

Set analysis with date between two date fields

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)

 

Labels (1)
7 Replies
Anil_Babu_Samineni

Perhaps this will reserve?
Count(distinct {$<[Last Appears]={"=[Last Appears]>=[Extract Date]"},[First Appears]={"=[First Appears]<=[Extract Date]"}>} [Candidate Number])
Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
lwestmaas
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

jonvitale
Creator III
Creator III

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
Creator III
Creator III

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
Contributor II
Contributor II

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.