Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
ZoeM
Creator III
Creator III

Greater than or Equal to with Set Analysis and 'and' expression

Hello Community.

I am having some trouble constructing an If Statement which has Set Analysis and an 'and' function, please see below:

If([Bottom or Top]='Top' and Only({$<[Date Type]={'VPP Dates'},Gateways={'PSC'}>}[Date]) < Only({$<[Date Type]={'AR Approval Date'}>}[Date]),'Late',
If([Bottom or Top]='Top' and Only({$<[Date Type]={'VPP Dates'},Gateways={'PSC'}>}[Date]) >= Only({$<[Date Type]={'AR Approval Date'}>}[Date]),'Not Late',

If([Bottom or Top]='Bottom' and Only({$<[Date Type]={'VPP Dates'},Gateways={'TSC'}>}[Date]) < Only({$<[Date Type]={'AR Approval Date'}>}[Date]),'Late',
If([Bottom or Top]='Bottom' and Only({$<[Date Type]={'VPP Dates'},Gateways={'TSC'}>}[Date]) >= Only({$<[Date Type]={'AR Approval Date'}>}[Date]),'Not Late'))))

There are results being populated but do not make sense based on what it should be. I am not sure if its because I am using the 'Only' function for the date?

Each line item should either be "Late" or "Not Late"

Labels (3)
1 Solution

Accepted Solutions
ZoeM
Creator III
Creator III
Author

Hello!

I have an update and resolution for this issue. 

One of my date types was incorrect from the data consolidation tool. After fixing that, the date field came in as intended and the expression works.

Thanks for all the feedback. 

View solution in original post

4 Replies
edwin
Specialist III
Specialist III

Actually, if all of the 4 sets of conditions are not met you will get a NULL.  so the statement only each line items should  either be Late or Not Late may not be accurate.

i suggest create a straight table with the fields 

[Bottom or Top],[Date Type], Gateways and in the end of the expression add 

Only({$<[Date Type]={'AR Approval Date'}>}[Date]),'Not Late','UNKNOWN'))))

and profile your data if some are unknown

ZoeM
Creator III
Creator III
Author

Thanks Edwin.

I do have a Straight Table with those individual fields as columns. I can see across the two date types that the result should either be Late or Not Late. Ideally, the field with Late/Not Late as selections will replace the two Date fields so that the end user does not have to evaluate on their own but can see if the line item is late or not. Just not sure why the syntax is not working

edwin
Specialist III
Specialist III

without seeing it, its hard to say what could be the cause or what is even the issue

when you say There are results being populated but do not make sense based on what it should be only you can see what those results are and its hard to give an opinion.  best is to share a QVW

ZoeM
Creator III
Creator III
Author

Hello!

I have an update and resolution for this issue. 

One of my date types was incorrect from the data consolidation tool. After fixing that, the date field came in as intended and the expression works.

Thanks for all the feedback. 

View solution in original post