topic Re: how to optimize set analysis expression? in QlikView

how to optimize set analysis expression?

shruthibk — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

could anybody help me to optimize the expression?

the expression is similar like below

=count({<Num_flag={'1'},rating={'high'},Year={'$(vCurrentYear)'},

[ COB Date],[Audit Date],[Month Name],issue=state1::issue>}DISTINCT(ID))

count({<DeNum_flag={'1'},rating={'high'},Year={'$(vCurrentYear)'},

[ COB Date],[Audit Date],[Month Name],issue=state1::issue>}DISTINCT(ID))

is there any way to optimize this code?

2) Dimensions are rating and Month Name

=count({$*state1<Num_flag={'1'}, Year={'$(=num(vCurrentYear)-1)'},[ COB Date],[Audit Date],[Month Name],

issue=state1::issue

>}DISTINCT ID)

count({$*state1<deNum_flag={'1'}, Year={'$(=num(vCurrentYear)-1)'},[ COB Date],,[Audit Date],[Month Name],

issue=state1::issue

>}DISTINCT ID)

i would like to exclude the Month Name dimesion in this expression,where this expression should be calculated for only rating ,how can i exclude this dimension?

Re: how to optimize set analysis expression?

Gysbert_Wassenaar — Fri, 26 Aug 2016 15:31:24 GMT

the expression is similar like below

1)

=count({<Num_flag={'1'},rating={'high'},Year={'$(vCurrentYear)'},

[ COB Date],[Audit Date],[Month Name],issue=state1::issue>}DISTINCT(ID))

/

count({<DeNum_flag={'1'},rating={'high'},Year={'$(vCurrentYear)'},

[ COB Date],[Audit Date],[Month Name],issue=state1::issue>}DISTINCT(ID))

is there any way to optimize this code?

You can remove the parentheses around ID. Otherwise? No idea. For some reason I don't seem to be able to open your Qlikview .qvw document.

2) Dimensions are rating and Month Name

=count({$*state1<Num_flag={'1'}, Year={'$(=num(vCurrentYear)-1)'},[ COB Date],[Audit Date],[Month Name],

issue=state1::issue

>}DISTINCT ID)

/

count({$*state1<deNum_flag={'1'}, Year={'$(=num(vCurrentYear)-1)'},[ COB Date],,[Audit Date],[Month Name],

issue=state1::issue

>}DISTINCT ID)

i would like to exclude the Month Name dimesion in this expression,where this expression should be calculated for only rating ,how can i exclude this dimension?

Exclude? I assume you don't mean simply removing it from the expression. Your using Month Name as chart dimension. That means the expression is calculated for the Month Name values. If you want to see the same value for every rating value then add Total <rating> to the expression.

=count(total <rating> {$*state1<Num_flag={'1'}, Year={'$(=num(vCurrentYear)-1)'},[ COB Date],[Audit Date],

issue=state1::issue

>}DISTINCT ID)

count(total <rating> {$*state1<deNum_flag={'1'}, Year={'$(=num(vCurrentYear)-1)'},[ COB Date],[Audit Date],

issue=state1::issue

>}DISTINCT ID)

Re: how to optimize set analysis expression?

shruthibk — Mon, 29 Aug 2016 07:33:49 GMT

the expression is giving null values