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

[gerrycastellino]

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.

[swuehl · Accepted solution]

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

  )

[swuehl]

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.