topic Re: expression QV in QlikView

expression QV

master_student — Mon, 13 Jun 2016 10:21:38 GMT

Hello Community,

could anyone explain to me this expression please :

=if( ValueList('Dotations', 'Transformations')='Dotations',

Sum({< CANAL_PA=, CODE_PDV=, PA_TYPE=,TYP_ENGAGEMENT= >} [Bonus accordé] )*num('0,0029') ,

Sum({< CANAL_PA=, CODE_PDV=, PA_TYPE=,TYP_ENGAGEMENT= >}[LP Value])*num('0,0029')

)

Re: expression QV

avinashelite — Mon, 13 Jun 2016 10:30:13 GMT

if your custom created dimension using ValueList, is 'Dotations', when part one will execute

Sum({< CANAL_PA=, CODE_PDV=, PA_TYPE=,TYP_ENGAGEMENT= >} [Bonus accordé] )*num('0,0029')

i.e sum of [Bonus accordé] ignoring the selection of the fields CANAL_PA, CODE_PDV, PA_TYPE,TYP_ENGAGEMENT

and then finally multiple my 0,0029 values

if the selection is not equal to Dotations, then Second part of the condition will get execute

Sum({< CANAL_PA=, CODE_PDV=, PA_TYPE=,TYP_ENGAGEMENT= >}[LP Value])*num('0,0029')

i.e sum of [LP Value] ignoring the selection of the fields CANAL_PA, CODE_PDV, PA_TYPE,TYP_ENGAGEMENT

and then finally multiple my 0,0029 values

Re: expression QV

miguelbraga — Mon, 13 Jun 2016 10:38:52 GMT

Hello master_student

Your expression is simple, and the answer is from Avinash R. When you understand it, you could try to make a Flag with this expression:

if( ValueList('Dotations', 'Transformations')='Dotations', 1, 0) as Flag_Dotations,

It's a best practice to make flags and then make a sum with set analysis like this:

Sum({<Flag_Dotataion = '1', CANAL_PA=, CODE_PDV=, PA_TYPE=,TYP_ENGAGEMENT= >} [Bonus accordé] )*num('0,0029') +

Sum({<Flag_Dotataion = '0',CANAL_PA=, CODE_PDV=, PA_TYPE=,TYP_ENGAGEMENT= >}[LP Value])*num('0,0029')

Also see the information for ValueList from reference manual:

ValueList (value {, value })

Returns a set of listed values which, when used in a calculated dimension, will form a synthetic dimension. In charts with a synthetic dimension created with the valuelist function it is possible to reference the dimension value corresponding to a specific expression cell by restating the valuelist function with the same parameters in the chart expression. The function may of course be used anywhere in the layout, but apart from when used for synthetic dimensions it will only be meaningful inside an aggregation function.

Examples:

valuelist ( 1, 10, 100 )

valuelist ( 'a', 'xyz', 55 )

Hope this helps you

Regards,

Re: expression QV

master_student — Mon, 13 Jun 2016 10:42:41 GMT

if the selection is not equal to Dotations , what do you mean by that, Dotation is a custom dimension, how we can select it?

Thanks

Re: expression QV

miguelbraga — Mon, 13 Jun 2016 10:46:56 GMT

Hey there,

Presumabily when you call something with quotes you refer as a value inside of a dimension and not the dimension, if I'm getting what you mean, I'll tell you, you have a mistake in your expression. What you're trying to achieve?

Regards,

Re: expression QV

master_student — Mon, 13 Jun 2016 11:04:16 GMT

Hi Miguel,

Thanks for your reply.

I had not created this expression, I am trying to understant it.

What I understant is with the Valuelist function, we can create a custom dimension

in my case, dimension= Dotations, values of dimension : 'Dotations', 'Transformations'

but how we can select 'Dotations', 'Transformations' , is there another dimension that contains those values?

Thanks