expression QV

[master_student]

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')

  )

[avinashelite · Accepted solution]

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

[miguelbraga]

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,

MB

[master_student]

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

[miguelbraga]

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,

MB

[master_student]

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