How to use greater than and less than condition in set expression in qlik sense

[vinay1083]

Hi,

Am trying to use use greater than and less than conditions in set expression as below.

I have written condition like below but it not working. Can any one help the syntax for this.

count({$<CTRLGAP_DAYS_UNTIL_PAST_DUE ={"<0"},CTRLGAP_REVW_STATUS={"Control Gap Closed","Control Gap Open"} >} [CONTROL GAP ID] )

[Or]

As covered in the documentation:

Looks like that's what you're using, so I don't see a problem.

[BrunPierre]

Hi, ensure that the field names and values match exactly as in the data model.

[vinay1083]

Hi,

Yes, exact field names and values I have taken from model itself.

[Or]

Perhaps the field in question is textual rather than numeric?

[vinay1083]

Hi,

count({$<CTRLGAP_DAYS_UNTIL_PAST_DUE ={"<0"},CTRLGAP_REVW_STATUS={"Control Gap Closed","Control Gap Open"} >} [CONTROL GAP ID] )

"CTRLGAP_DAYS_UNTIL_PAST_DUE" this field contains numeric values with positive and negative numbers

"CTRLGAP_REVW_STATUS" this field contains textual values

"[CONTROL GAP ID]" this filed contains combination of char-numeric values

Looking forward to your answer and thank you in advance!

[Or]

As I said, the syntax looks fine. I'd suggest confirming that the days until past due field is actually being read as  numeric rather than text, since a textual search would return different values than a numeric one in this context. Other than that, no particular ideas.

[BrunPierre]

If the issue persists, you may need to provide sample data for further assistance.

[vinay1083]

Hi,

I have attached the sample data excel here.

Hope so this will helpful.

Thank you

[anat]

can you create flag in load script like

if(CTRLGAP_DAYS_UNTIL_PAST_DUE>0,1,0) as CTRLGAP_DAYS_UNTIL_PAST_DUE_FLAG

then try below set expression:

count({$<CTRLGAP_DAYS_UNTIL_PAST_DUE_FLAG ={1},CTRLGAP_REVW_STATUS={"Control Gap Closed","Control Gap Open"} >} [CONTROL GAP ID] )