topic Re: Conditional script variable in QlikView

Conditional script variable

Anonymous — Sun, 30 Sep 2018 21:00:37 GMT

Hello all,

I need to create a variable with conditions:

This way bellow is not working:

let vmeta_dm = if([Grupo Equip.]= 'Forwarder' and Trimestre = '1º Trimestre',avg([Meta DM]));

Could you guys help.

tks

Re: Conditional script variable

swuehl — Sun, 30 Sep 2018 21:10:14 GMT

In which context do you want to use this variable?

If you want to define the variable in the script, but then use the variable e.g. in a UI chart expression (assumingly with dimensions [Grupo Equip.] and Trimestre), i.e. using a dollar sign expansion $(vmeta_dm), then you can define your variable like

SET vmeta_dm = if([Grupo Equip.]= 'Forwarder' and Trimestre = '1º Trimestre',avg([Meta DM]));

If you want the variable to contain the avg([Meta DM]) for the conditions specified, then this won't work, you can't access and aggregate field values like this.

Re: Conditional script variable

Anonymous — Sun, 30 Sep 2018 21:21:30 GMT

tks 4 reply Stefan.

I wanna create the variable to calculate a total amount of fee for each type of machine and trimester:

the amount should be: 814.732.

In single table with sum of lines if works. But i need the final result in a text box.

I'm getting null value in a text box with the e.g. you gave me:

=$(vmeta_dm)

if I create a variable like this with set analysis out of script it works:

=avg({1<[Grupo Equip.]={'Forwarder'},Trimestre={'1º Trimestre'}>} [Meta DM])

is this the best way to do that?

Re: Conditional script variable

swuehl — Sun, 30 Sep 2018 21:29:27 GMT

The set analysis looks fine.

If you want to use the first expression, remember to

Use Aggregation Functions!

So, try using the conditional in the aggregation, not outside of the aggregation function (that's the reason why the total line or the text box does not work as expected):

avg(if([Grupo Equip.]= 'Forwarder' and Trimestre = '1º Trimestre', [Meta DM]))