topic Re: Problem using greater than or equal in set analysis in QlikView

Problem using greater than or equal in set analysis

Anonymous — Tue, 27 Oct 2015 19:48:09 GMT

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

Re: Problem using greater than or equal in set analysis

swuehl — Tue, 27 Oct 2015 20:03:13 GMT

Try

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

Re: Problem using greater than or equal in set analysis

maxgro — Tue, 27 Oct 2015 20:13:47 GMT

maybe without SA

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

Re: Problem using greater than or equal in set analysis

PrashantSangle — Wed, 28 Oct 2015 04:41:29 GMT

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.

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,

Re: Problem using greater than or equal in set analysis

arasantorule — Wed, 28 Oct 2015 07:16:50 GMT

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.

Re: Problem using greater than or equal in set analysis

Anonymous — Wed, 28 Oct 2015 13:59:43 GMT

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

Re: Problem using greater than or equal in set analysis

swuehl — Wed, 28 Oct 2015 16:16:17 GMT

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"}>}

Re: Problem using greater than or equal in set analysis

PrashantSangle — Thu, 29 Oct 2015 04:18:33 GMT

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,