topic Re: Looking for more elegant way of my solution - can dimension be passed as parameter to expression in QlikView

Looking for more elegant way of my solution - can dimension be passed as parameter to expression

gerrycastellino — Mon, 09 Nov 2015 22:17:13 GMT

I have a series of if statements in my expression where I need to calculate the average occurrence of the counts of the dimension values,

I'm not sure how else to do it ?

if(Ratings=1,(count( {<HelpDesk= {1} >} HelpDesk) + count({<Email={1} >} Email))/2,

if(Ratings=2 ,(count( {<HelpDesk= {2} >} HelpDesk) + count({<Email={2} >} Email))/2,

if(Ratings=3 ,(count( {<HelpDesk= {3} >} HelpDesk) + count({<Email={3} >} Email))/2,

if(Ratings=4 ,(count( {<HelpDesk= {4} >} HelpDesk) + count({<Email={4} >} Email))/2,

))))

Is there a way, via a set expression to pass the value from my dimension to my expression ?

Gerry.

Re: Looking for more elegant way of my solution - can dimension be passed as parameter to expression

swuehl — Mon, 09 Nov 2015 22:42:15 GMT

Set analysis is only evaluated once per chart, not per dimension value, so you can't pass the dimension value to it.

Alternatively to the nested if() statements, you can use pick() function:

=Pick(Ratings,

(count( {<HelpDesk= {1} >} HelpDesk) + count({<Email={1} >} Email))/2,

(count( {<HelpDesk= {2} >} HelpDesk) + count({<Email={2} >} Email))/2,

(count( {<HelpDesk= {3} >} HelpDesk) + count({<Email={3} >} Email))/2,

(count( {<HelpDesk= {4} >} HelpDesk) + count({<Email={4} >} Email))/2

)

Re: Looking for more elegant way of my solution - can dimension be passed as parameter to expression

swuehl — Mon, 09 Nov 2015 23:10:05 GMT

And if it's possible to remodel your main data table from a crosstable to a straight table using CROSSTABLE LOAD prefix, you can generate the Rating from your data and avoid the conditional expression evaluation completely. See attached for a sample.