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: 
Anonymous
Not applicable

Date comparison in Set Analysis

I have two date columns and am trying to count number of occurrence for first date > second date + 3 days.

When I use following in a KPI, the approach 1 works, but second approach that uses set analysis always brings back zero.


Approach 1: works

=Count(If (AddDte > Date(TrnDte+3), WebId))

Approach 2: does not work

=Count({$<AddDte={"$(>Date(TrnDte+3))"}>} WebId)

Is the above set analysis not correctly defined?

Also is there a way to debug set analysis in QlikSense Desktop?

6 Replies
Gysbert_Wassenaar

To begin with the syntax is incorrect. It should be =Count({$<AddDte={">$(=Date(TrnDte+3))"}>} WebId).

But there are probably several different TrnDte values possible for a single AddDte value. The set analysis expression calculates the set at the chart level, not a the record level. And add the chart level is can't determine whiich records should be in the set since at that level it cannot determine which TrnDte value from the possible values should be used to compare with AddDte. Perhaps this works: =Count({$<WebId={"=AddDte>$(=Date(TrnDte+3))"}>} WebId)


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Both of the above suggestions give zero count.

Also tried following but that counts all records in the table-

=Count({$<3={"<$(=Interval(AddDte-TrnDte,'d'))"}>} WebId)

sunny_talwar

I think gwassenaar‌ meant to use this for the second expression, right?

=Count({$<WebId = {"=AddDte > TrnDte+3"}>} WebId)

JustinDallas
Specialist III
Specialist III

I just spent forever trying to figure this out and it's not even my problem.  Thanks again Sunny.

Anonymous
Not applicable
Author

Thanks Sunny and Justin for looking into it.

Please help me understand the expression you have suggested so that I can continue to tweak it to get right count.

Or point me to documentation that can explain '$<WebId = {"=AddDte > TrnDte+3"}>' part.

sunny_talwar

All we are suggesting is a search string where you pick those WebId where AddDte > TrnDte + 3. You can read about this concept here

A Primer on Set Analysis

Also, look at HIC's reponse here

Re: Why i am not getting same result with Above function ?