Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik experts,
I had below set expression which provides count as '2'.
It is a set expression ignoring current selections though there is a selection on field %_SITEID.
=count({1<
CALCULATED_SITE_STATUS={"Active"},
%_SITEID= {"194","201"}
>} DISTINCT %_SITEID)
Same expression with an if clause gives result as '0'.
=count({1<
CALCULATED_SITE_STATUS={"Active"},
%_SITEID= {"=if(match(%_SITEID,194,201),1,0)=1"}
>} DISTINCT %_SITEID)
Any advise how to use an if clause inside set expression and by ignoring current selections though there exists a selection on field used inside set expression if clause.
Thanks in advance.
Not sure I understand, but may be this
=Count({1<CALCULATED_SITE_STATUS = {"Active"}, %_SITEID = {"=If(Match(Only({1} %_SITEID), 194, 201), 1, 0) = 1"}
>} DISTINCT %_SITEID)
store if condition in variable
and then use that in set expression
try this,
count({1<
CALCULATED_SITE_STATUS={"Active"},
%_SITEID= {"=if(match(%_SITEID,'194','201'),%_SITEID)"}
>} DISTINCT %_SITEID)
Br,
KC
try this
if(match(%_SITEID,'194','201'),
count({1<
CALCULATED_SITE_STATUS={"Active"},
>} DISTINCT %_SITEID)
Hi,
Can you upload some sample data?
Not sure I understand, but may be this
=Count({1<CALCULATED_SITE_STATUS = {"Active"}, %_SITEID = {"=If(Match(Only({1} %_SITEID), 194, 201), 1, 0) = 1"}
>} DISTINCT %_SITEID)
Thanks all.
Sunny, yes Only{1} inside match did the magic. Thanks.
I have also tried other advises below where the result is not as expected.
Gives -0
If clause in a variable and use variable in set instead.
Gives - 0
count({1<
CALCULATED_SITE_STATUS={"Active"},
%_SITEID= {"=if(match(%_SITEID,'194','201'),%_SITEID)"}
>} DISTINCT %_SITEID)
Gives - '-'
if(match(%_SITEID,'194','201'),
count({1<
CALCULATED_SITE_STATUS={"Active"},
>} DISTINCT %_SITEID)
To provide a view of sample data for others visiting this post -
MYTABLE:
LOAD
*
INLINE
[
%_SITEID,CALCULATED_SITE_STATUS
194,Active
201,Active
205,Active
206,Active
212,Inactive
214,Inactive
];
Step 1 : A list box on sheet for %_SITEID
Step2 : Select %_SITEID as 205
Step3: Requirement is to give me count of only records where %_SITEID is 194 or 201 and with CALCULATED_SITE_STATUS as Active though selection of %_SITEID is for 205.
It is underlying scenario i was looking for though it is a subset of bigger set query.
Thanks all.