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

Problem using greater than or equal in set analysis

I've searched and read suggestions but nothing has worked yet.

I'm using 11.20 SR 7 on Windows 8.1

I'm trying to use set analysis in an expression in a straight table.

This works:

SUM({$<APPLICATION_TERM_CNENR={"2015B","2015C","2016A"}>} DEPOSIT_AMT_PAID_CNENR)

I wanted to do something like:

SUM({$<APPLICATION_TERM_CNENR>={"2015B"}>} DEPOSIT_AMT_PAID_CNENR)

It makes sense it wouldn't work because the > would be interpreted as the end of the search condition not as greater than.

Things like the following don't work (trying single and double quotes)

SUM({$<APPLICATION_TERM_CNENR={'>="2015B"'}>} DEPOSIT_AMT_PAID_CNENR)

or SUM({$<APPLICATION_TERM_CNENR={">='2015B'"}>} DEPOSIT_AMT_PAID_CNENR)

I was surprised that the following didn't work, it seemed like there were examples nearly like that.

SUM({$<left(APPLICATION_TERM_CNENR,4)={">=2015"}>} DEPOSIT_AMT_PAID_CNENR)

It needs to see >= as a comparison and then 2015B as a text string.

What do I misunderstand here?

John

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

SUM({$<APPLICATION_TERM_CNENR = {"=APPLICATION_TERM_CNENR follows '2015A'"}>} DEPOSIT_AMT_PAID_CNENR)

View solution in original post

7 Replies
swuehl
MVP
MVP

Try

SUM({$<APPLICATION_TERM_CNENR = {"=APPLICATION_TERM_CNENR follows '2015A'"}>} DEPOSIT_AMT_PAID_CNENR)

maxgro
MVP
MVP

maybe without SA

SUM(if(APPLICATION_TERM_CNENR >= '2015B', DEPOSIT_AMT_PAID_CNENR))

PrashantSangle

Hi,

You can not compare string using numeric operator.

Instead of this you can try below

1: Create new field in script

left(APPLICATION_TERM_CNENR,4) as New_fieldName,

2: Use above field in your set analysis.

like

SUM({$<New_fieldName={">=2015"}>} DEPOSIT_AMT_PAID_CNENR)

Note : In Set Analysis you can not use Calculated field on Left side of =.

           For that you have to create calculated field in script and use it.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
arasantorule
Creator III
Creator III

Hi,

Relational operator cannot be used to compare strings. Hence you can split the Filed into two, one will be holding say Year='2015' and Order or Sequence='A'.

Then you will be able to achieve this by comparing those fields. you can use string functions to split the column into two.

In fact we can make the comparison more generic if you have the field splitted into two.

Thanks.

Anonymous
Not applicable
Author

This works. I don't understand why it works. I haven't found documentation of the 'follows' comparison.

Why does {"=APPLICATION_TERM_CNENR follows '2015A'"} work as the right side of the comparison? It seems like it would be evaluated as 'YES' 'NO'.

Is there documentation anywhere of this sort of comparison?

John

swuehl
MVP
MVP

precedes and follows are explained in section 'relational operators' in the HELP.

In contrary to max dreamer, I think this should also work:

SUM({$<APPLICATION_TERM_CNENR = {"=APPLICATION_TERM_CNENR > '2015A'"}>} DEPOSIT_AMT_PAID_CNENR)

And the search expression is indeed returning true / false per field value of APPLICATION_TERM_CNENR, similar to an advanced search like

{<Customer = {"=sum(Sales) >0"}>}

PrashantSangle

Hi,

Awesooooooooooome swuehl

Follows and Precedes are perfect suggestion.

I have never tried Follows and Precedes,

Thanks Swuehl for highlighting this relational operator.

You are truly Legend.

Just one request, If you have any book or any document which gives us idea about all functions. then Please share.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂