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

Chart Expression with IF clause, fractile, and variable substitution

jwaligora — Sat, 16 Nov 2024 03:02:42 GMT

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.

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

rubenmarin — Wed, 10 Jul 2019 21:07:00 GMT

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.

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

jwaligora — Wed, 10 Jul 2019 21:32:13 GMT

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

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

jwaligora — Thu, 11 Jul 2019 18:55:22 GMT

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

rubenmarin — Fri, 12 Jul 2019 06:09:11 GMT

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

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

jwaligora — Fri, 12 Jul 2019 16:04:37 GMT

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

rubenmarin — Mon, 15 Jul 2019 06:13:23 GMT

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.

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

jwaligora — Mon, 15 Jul 2019 17:41:41 GMT

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

You're pretty slick. Thanks!