Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

jwaligora
New Contributor III

Chart Expression with IF clause, fractile, and variable substitution

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.

Labels (4)
1 Solution

Accepted Solutions

Re: Chart Expression with IF clause, fractile, and variable substitution

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.

7 Replies

Re: Chart Expression with IF clause, fractile, and variable substitution

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
New Contributor III

Re: Chart Expression with IF clause, fractile, and variable substitution

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

Highlighted
jwaligora
New Contributor III

Re: Chart Expression with IF clause, fractile, and variable substitution

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.

Re: Chart Expression with IF clause, fractile, and variable substitution

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

jwaligora
New Contributor III

Re: Chart Expression with IF clause, fractile, and variable substitution

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.

Re: Chart Expression with IF clause, fractile, and variable substitution

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
New Contributor III

Re: Chart Expression with IF clause, fractile, and variable substitution

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

You're pretty slick. Thanks!