Assigning Expressions within the load script

[richard_pearce6]

Hi,

We have a requirement where a number of metrics defined within the load script have their own expression. For example:

Measure          ,Expression

Total               ,Sum(Sales)

Average          ,Avg(Sales)

We've created a chart where the Measure is the dimension and the expression is =only(Expression)

I've tried various combinations of dollar expansion and not been able to get it working.

Can someone provide assistance???

Thanks

Richard

[#2]

Are you able to provide an example?

[richard_pearce6]

Example attached

[stigchel]

I have used this successfully, first of all you need to make sure that only 1 value in Expression is valid and then use this as expression:

=$(=Expression)

Mind the $(=

[richard_pearce6]

Hi,

I've used this before with a single expression although wanted to be able to use several. Have you managed to do this type of thing before?

Really its the same as using the expression below although we want to make it feed automatically from the database (as field expression). I could create a script that creates the expression varaible as per below but wondered if there was a better way?

if

(Measure='Average',avg(Sales)
,if(Measure='Total',Sum(Sales)))

[#6]

Not sure if you can it this way with multiple expressions like that, if I was going to do something like this I would use a combination of pick and match functions instead of an If statement.

=pick(match(Measure, 'Average','Total'),Avg(Sales),Sum(Sales))

[stigchel · Accepted solution]

Yes sorry, I typed it before you attached the example. I used it for a single expression as well. The problem here is that the dollar sign expansion is only assigned once for the whole chart and not per dimension value. Without it the expression is not evaluated

[richard_pearce6]

I wasn't sure either Joe. My alternative works although Pick and Match may be a faster calculation time... Alternativly I may be able to pre-calculate in the script depending on other requirements.....

If anyone has made this work please let me know. Or, if you know the technical QlikView reason wy not I'd be interested in knowing that also.

Cheers

[martynlloyd]

Why not use conditional expressions, based on a variable vMeasure?

It is how Qlik do it in their What's New in QlikView 11 example app...

K.I.S.S.

Regards.

[richard_pearce6]

Ah that makes perfect sense. !! So it's calculated as a null value which is exactly what I am seeing. I honestly didn't think it was possible but couldn't think of the technical reason (and sometimes you never know, it may have been possible)..

Cheers !!!!