Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jwaligora
Creator
Creator

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
rubenmarin

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.

View solution in original post

7 Replies
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
Creator
Creator
Author

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

jwaligora
Creator
Creator
Author

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
Creator
Creator
Author

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

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.

View solution in original post

jwaligora
Creator
Creator
Author

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

You're pretty slick. Thanks!