if to SET

[priyarane]

Hi Community,

Can we modify below expression in set analysis

if (len (REFERENCE) = 0 and TYPE <> 'OFB', sum({<%Month = {'$(=$(=vCurrMonth))'}, %Year = {'$(=$(=vCurrYear))'}>}PAS),0)

[gautik92]

'i hope this will work

sum({<%Month = {'$(=$(=vCurrMonth))'}, %Year = {'$(=$(=vCurrYear))'},len (REFERENCE) = {'0}and TYPE <> {'OFB'}>}PAS)

[tresesco]

Try like:

sum({< REFERENCE = {"=Len(REFERENCE)=0"}, TYPE - = {OFB}, %Month = {'$(=$(=vCurrMonth))'}, %Year = {'$(=$(=vCurrYear))'}>}PAS)

[Kushal_Chawda]

try

sum({< REFERENCE = {"=Len(trim(REFERENCE))=0"}, TYPE - = {'OFB'}, %Month = {'$(=$(=vCurrMonth))'}, %Year = {'$(=$(=vCurrYear))'}>}PAS)

[jonathandienst]

An If before the aggregation expression (sum() in this case) works differently than an if condition inside the aggregation.

• An if inside the expression is a filter and can usually be replaced with set expressions (but watch out for nulls).
• An if outside the aggregation is a conditional calculation, which is not easily replaced by a set expression.

So your requirement is not clear. This expression

Sum({<%Month = {'$(=$(=vCurrMonth))'}, %Year = {'$(=$(=vCurrYear))'}>} if (len (REFERENCE) = 0 and TYPE <> 'OFB', PAS,0))

may be converted -- this what the posts above have done; but this expression

if (len (REFERENCE) = 0 and TYPE <> 'OFB', sum({<%Month = {'$(=$(=vCurrMonth))'}, %Year = {'$(=$(=vCurrYear))'}>}PAS),0)

cannot be easily converted as the If() is not a filter.

[priyarane]

Below conditions should match for PAS

"REFERENCE" must be blank

"TYPE"  not equal to "OFB"

[jonathandienst]

But as a filter (ie sum all that match the filter) or as a calculation condition, result is sum only if matching, otherwise zero?