Problem using greater than or equal in set analysis

[Anonymous]

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

[swuehl · Accepted solution]

Try

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

[maxgro]

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,

[arasantorule]

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]

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]

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,