Help With Set Analysis

[#1]

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

[#2]

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

[tresesco]

=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).

[jonathandienst]

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

[#5 · Accepted solution]

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

[#6]

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