topic Help With Set Analysis in QlikView

Help With Set Analysis

Tue, 05 May 2015 12:58:07 GMT

Hi Guys

Looking for some Set Analysis experts please

Please can someone tell me what is wrong with this expression please?

=Money(sum({1<Year(LOAD_DATE)={"$(=Year(Today()-10))"}>}(POSTED_DEBIT-POSTED_CREDIT)))

Just cannot see what is wrong with this

Although the expression builder says "Expression OK", I am getting the error "Error: Error in set modifier expression"

Thanks

John

Re: Help With Set Analysis

Tue, 05 May 2015 13:04:43 GMT

Hi John,

set analysis needs to be based on real fields, so using Year(LOAD_DATE) as a modifier won't work.

If you create a year field in your model, you can then use that

year(LOAD_DATE) as LOAD_YEAR

in the script

then expression

Money(sum({1<LOAD_YEAR={"$(=Year(Today()-10))"}>}(POSTED_DEBIT-POSTED_CREDIT)))

hope that helps

Joe

Re: Help With Set Analysis

tresB — Tue, 05 May 2015 13:05:14 GMT

=Money(sum({1<Year(LOAD_DATE)={"$(=Year(Today()-10))"}>}(POSTED_DEBIT-POSTED_CREDIT)))

The highlighted part is the one causing issue. You can't compare a calculated dimension/field in set analysis. You can only use script generated fields in set comparison (left hand side of '=' sign).

Re: Help With Set Analysis

jonathandienst — Tue, 05 May 2015 13:06:05 GMT

The LHS of a set expression can only be a field name, not an expression. I would define a year field in the load script like this:

LOAD ....

Year(LOAD_DATE) As LoadYear,

...

Then use:

=Money(sum({1<LoadYear = {"$(=Year(Today()-10))"}>} (POSTED_DEBIT-POSTED_CREDIT)))

HTH

Jonathan

Re: Help With Set Analysis

Tue, 05 May 2015 13:06:35 GMT

Also rather than using the two fields in the sum, you might want to consider doing that in the script also and then the sum can be based on a single field. Just makes it performan better on the front end that way

Re: Help With Set Analysis

Tue, 05 May 2015 13:17:23 GMT

Thanks VERY much everyone, great answers very clear, and MUCH appreciated.