Pick inside set analysis

[Anonymous]

Hi,

I am trying to build a button where user can choose product and then all the graphs on the sheet changes according to that.

I have set product dimension in the load script in the following way:

Set VProductDim = 1;

and then in the variable extension I have given values and labels for that variable. This worked in other app where I just changed the dimensions according to the variable.

But now I am trying to create set analysis using the variable values as a limitation. Like this:

sum({<PRODUCT ={"=pick($(VProductDim),'Product1','Product2','Product3')" } >}TURNOVER_EUR)

Could someone explain why is this giving me nothing, or is it even possible to do it this way?

Best Regards.

[marcus_sommer · Accepted solution]

Try it in this way:

sum({<PRODUCT ={"$(=pick($(VProductDim),'Product1','Product2','Product3'))" } >}TURNOVER_EUR)

but wouldn't it be easier if the user just selects which products he/she want to see?

- Marcus

[Anonymous]

Hi,

Thanks a lot for the help. It works now.

I want to do it this way because I use several dimensions on this sheet and by clicking the product button they see overall picture of this product by one look.

Br

Matti

[Anonymous]

Hi,

I got a new problem to this one. What if I would like to have an option that no specified product is shown.

so the first value in pick() would be all of those three products.

I tried:

sum({<PRODUCT ={"$(=pick($(VProductDim),'  ','Product1','Product2','Product3'))" } >}TURNOVER_EUR)

and

sum({<PRODUCT ={"$(=pick($(VProductDim),' {'Product1','Product2','Product3'} ','Product1','Product2','Product3'))" } >}TURNOVER_EUR)

But could get this solution working.

[marcus_sommer]

It depends a bit on the possible variable-values but I could in regard to your example imagine something like this:

sum({<PRODUCT ={"$(=pick(rangemin($(VProductDim), 4),'Product1','Product2','Product3', '*'))" } >}TURNOVER_EUR)

- Marcus

[Anonymous]

Hi,

I didnt get the right result with that one. Now I tried to create a variable and use that inside pick().

Like this:

Set  VDummy = {'Product1','Product2','Product3'};

I insert the pick function with the variable into a text box and it works fine.

But inside the set analysis I get no values when choosing the "All" button, which should be these 3 products.

My set analysis is like this now:

sum({<PRODUCT ={"$(=pick($(VProductDim),'VDummy' ,'Product1','Product2','Product3'))" } >}TURNOVER_EUR)

With other buttons for the products it works nicely and correctly. Do you know if it is even possible to use variable like that inside pick()?

My logic there is that the pick() should return this '{'Product1','Product2','Product3'}'

If that would be hard coded into set analysis it would work, but for some reason with pick() does not work.

Br

Matti

[marcus_sommer]

The $-sign expansion of $(= ...) within the set analysis is quite the same like creating a variable unless they isn't stored within the variable-list else they will be adhoc created with each call. This meant that you could create this as a variable outside from the expression whereby in this case you will need a let statement instead of a set.

In your case it's adverse to use this kind of syntax because you have already {} brackets for the set analysis and the second pair within VDummy will make it invalid but without there would be also none correct string to adress all values. Further also your call of 'VDummy' won't be interpreted as variable and therefore working.

For this reason my previous suggestion was to adress all values with a wildcard of * An alternatively might be a different way to write the values within a search-string like:

Set  VDummy = ("Product1"|"Product2"|"Product3");

sum({<PRODUCT ={"$(=pick($(VProductDim),'$(VDummy)' ,'Product1','Product2','Product3'))" } >}TURNOVER_EUR)

I'm not sure but I have the impression that you tend to make easy things a bit more complicated as necessary ... but if you want to proceed with this approach I suggest to have a look here: Variables

- Marcus