Complex Set Analysis

[rudywelvaert]

Hi,

I like to write a set analysis  to replace following expression:

=Sum(If(AMOUNT_DOSSIER > 25000
and FUNCTION='ABC' 
and (QUARTER_COUNT = $(vYearCount) & ' Q4' or DATE_COUNT = $(vLastDateCount)) 
and YEAR_COUNT=$(vYearCount)
, NUMBER_DOSSIERS))

Can this be done? And how?

Thanks

R.W.

[sunny_talwar]

May be this

=Sum({<AMOUNT_DOSSIER = {">25000"}, FUNCTION = {'ABC'}, QUARTER_COUNT = {"$(=$(vYearCount) & ' Q4')", {"$(=$(vLastDateCount))"}, YEAR_COUNT = {"$(=$(vYearCount))"}>} NUMBER_DOSSIERS)

[vishsaggi]

Sunny for vLastDateCount they are using a Date_Count field? Do not we need to add that field too in the set?

[rudywelvaert]

Sunny,

Thanks

But this doesn't work.

The field  DATE_COUNT is missing

or DATE_COUNT = $(vLastDateCount)

There is an or statement that tests on two different fields: QUARTER_COUNT and DATE_COUNT

R.W.

[vishsaggi]

Extending Sunny's expr: Just add DATE_COUNT before vLastDateCount variable

=Sum({< AMOUNT_DOSSIER = {">25000"},
FUNCTION = {'ABC'},
QUARTER_COUNT = {"$(=$(vYearCount) & ' Q4')"},
DATE_COUNT = {"$(=$(vLastDateCount))"}, 
YEAR_COUNT = {"$(=$(vYearCount))"}
>} NUMBER_DOSSIERS
)

The above will not work

Follow Sunny's Set expr using operators.

[sunny_talwar]

Yes, you are right .. may be this

=Sum({

<AMOUNT_DOSSIER = {">25000"}, FUNCTION = {'ABC'}, QUARTER_COUNT = {"$(=$(vYearCount)& ' Q4')"}, YEAR_COUNT = {"$(=$(vYearCount))"}>

+

<AMOUNT_DOSSIER = {">25000"}, FUNCTION = {'ABC'}, DATE_COUNT = {"$(=$(vLastDateCount))"}, YEAR_COUNT = {"$(=$(vYearCount))"}>

} NUMBER_DOSSIERS)

[vishsaggi]

IS this because of OR ? So we cannot give just datecount right bcoz Set analysis default AND? Correct me?

[sunny_talwar]

Yes