Property value in CASE WHEN Statement

[Vincent Thuilot]

Hello

I'm trying to pass through a document property value in a chart with a CASE WHEN command but it does not work.  The property is called Metrics and is a single drop-down made up with several columns (text and numbers) in the analysis.

I would like to have a unique Count when the Metrics holds the value "Region", and sum otherwise.

Here what I am using:

case 

WHEN DocumentProperty("Metrics")="Region" then UniqueCount([Region])

else SUM($esc(${Metrics}))

END as [Metrics]

I've also tried this one but no success either:

case 

WHEN "${Metrics}"="Region" then UniqueCount([Region])

else SUM($esc(${Metrics}))

END as [Metrics]

Any idea what's wrong?

Whatever I write, it seems that it's not recognizing Region as text value and jumps directly to the SUM, which obviously can't be computed.

Thanks for your support

Vincent

[Gaia Paolini]

can you show an example of the Metrics document property? What are you summing when it is not equal to Region? You should be able to see a preview of how your expression is translated on the bottom-right, can you see it?

[Vincent Thuilot]

Sure, please find attached the parameter list and the 2 scenarios results (when the document property is set to Region and when it's not)

[Vincent Thuilot]

here what happens when Region is selected

[Vincent Thuilot]

and here when somethin else than region is selected

[Vincent Thuilot]

When having the parameter Region and using the expression with

WHEN "${Metrics}"="Region" then UniqueCount([Region])

We can see that it's correctly calculating in the preview, somehow it jumps directly to the SUM, as if as it was not recognized.

[Gaia Paolini]

What happens is that the whole expression is translated using the current value of the Metrics document property.

So when Metrics is equal to Region, this is the expression (in the preview):

case 
 WHEN "Region"="Region" then UniqueCount([Region]) 
 else SUM([Region])
 ENDcase WHEN "Region"="Region" then UniqueCount([Region]) else SUM([Region]) END

it tries to parse the expression and finds that you cannot evaluate Sum of Region, regardless of whether that statement is reached or not.

A way to fool the expression interpreter that worked for me:

case 
 WHEN "${Metrics}"="Region" then UniqueCount([Region]) 
 else SUM(  Real([${Metrics}]) )
 END

you are obviously not going to turn Region into real and sum it, but the interpreter seems satisfied that the expression is correct with respect to the data types used.

[Vincent Thuilot]

Amazing, it works!!

Thank you so much Gaia, it's such a great learning :)

It will definitely bring more flexibility in my future interactions with the interpreter. It was a bit weird because I usually use these kind of CASE WHEN statements with parameters but never faced it like this.

Is there a place where I can get further documentation on this topic of expression interpreter?

[Gaia Paolini]

I understand that first the entire expression is parsed to check its syntax. 

Then it is type-checked (see that data types match, functions exist). 

Then the expression is actually executed.

So in the case when the document property was equal to Region, the parser found that Region is a categorical data type and Sum([Region]) is invalid. Therefore the following expression throws an error, even though the second part (sum([REGION]) would never be reached during execution.

  CASE WHEN '${chosen column}'='REGION' THEN UniqueCount ([${chosen column}]) else Sum([${chosen column}]) end

But the modified expression below works. For all we know Region might have been a string that contained numbers, such as '1.32'. Therefore adding Real() around it makes it syntactically valid in principle - since the branch with Sum() is never reached in that case, we are ok. 

 CASE WHEN '${chosen column}'='REGION' THEN UniqueCount ([${chosen column}]) else Sum(Real(${chosen column}])) end