topic Re: if to SET in QlikView

if to SET

priyarane — Mon, 05 Oct 2015 10:15:14 GMT

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 — Mon, 05 Oct 2015 10:19:05 GMT

'i hope this will work

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

tresB — Mon, 05 Oct 2015 10:22:32 GMT

Try like:

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

Kushal_Chawda — Mon, 05 Oct 2015 10:35:13 GMT

try

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

jonathandienst — Mon, 05 Oct 2015 10:40:08 GMT

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 — Mon, 05 Oct 2015 11:36:38 GMT

Below conditions should match for PAS

"REFERENCE" must be blank

"TYPE" not equal to "OFB"

jonathandienst — Mon, 05 Oct 2015 11:52:23 GMT

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