Chart Expression with IF clause, fractile, and variable substitution

[jwaligora]

Hi,

I have a vCalc variable that holds ONE of: {'Sum','Min','Max','Avg','Median','95th'}

Consequently, I have 2 expression scenarios:

• $(vCalc)(aggr(max([BBNOSSE.Time Spent]), [BBNOSSE.PKey]))
• fractile(aggr(max([BBNOSSE.Time Spent]), [BBNOSSE.PKey]), 0.95)

with the former corresponding to {'Sum','Min','Max','Avg','Median'} subset, and the latter to the {'95th'} value.

Each of these work just fine individually, but strange things happen when wrapped in an IF clause.

Works without issues:

= if (
	vCalc = '95th', 
	fractile(aggr(max([Time Spent]), [RecordID]), 0.95),
	10
  )

Fails on (vCalc='95th') --> TRUE i.e. the 10;  (vCalc='95th') --> FALSE displays fine:

= if (
	vCalc = '95th', 
	10,
	$(vCalc)(aggr(max([Time Spent]), [RecordID]))
  )

As above, fails on (vCalc='95th') --> TRUE i.e. the fractile;  (vCalc='95th') --> FALSE displays fine:

= if (
	vCalc = '95th', 
	fractile(aggr(max([Time Spent]), [RecordID]), 0.95),
	$(vCalc)(aggr(max([Time Spent]), [RecordID]))
  )

(FAILS = "No data to display")

What obvious thing am I missing?

Thanks,
Joanna.

[rubenmarin]

Hi, I tested with: =if (vCalc = '95th', 10, 20 )

If vCalc has the '95th' value it returns 10, if vCalc has any other value it returns 20.

can you confirm that the variable really stores 95th when it fails? maybe it has any additional character that make it go to the FALSE, creating an invalid expression.

[jwaligora]

Hi. It definitely stores '95th', which is validated by the 1st code snippet.

[jwaligora]

I fudged a fix by scrapping the IF clause and turning this into 2 completely separate expressions. The vCalc<>'95th' /  vCalc='95th' now lives in the "Conditional" section of Expressions tab. I still have no idea why the original approach keeps failing.

[rubenmarin]

Hi, me neither, I can't reproduce the behaviour. If you can upload a sample I can take a look.

[jwaligora]

See attached. Stripped down to bare bones. 

Top graph uses the built-in condition (2 expressions). Works.

Bottom graph uses an  IF clause (1 expression). Works for only one of the 2 expressions.

[rubenmarin · Accepted solution]

Ok, I did a simpler test, the problem is bacuase 95th causes an invalid 2nd expression, so all the expression turns invalid, you can try an approach like:

=if (
vCalc = '95th',
fractile(aggr(max([Time Spent]), [ID]), 0.95),
$(=Replace('$(vCalc)', '95th', 'Sum'))(aggr(max([Time Spent]), [ID]))
)

So the 2nd expression  uses Sum when 95th is selected. It won't be calculated as a sum, it's just to avoid the invalid syntax error.

[jwaligora]

I seriously didn't expect it to evaluate the second expression on a 'true' condition.

You're pretty slick. Thanks!