pick function makes histogram and pivot table super slow, the app freezed for more than an hour

[luohda]

i use pick function to select the columns I want to show in charts, for line and combo charts it was very fast, but in histogram and pivot table it takes forever to load, so I close the tab, and try to reopen the app from hub, it just keeps loading, I dont even get to see the sheets.

i use pick in this way:

fact table:

load

other keys,

param_a,

param_b,

param_c

from qvd;

param:

load * inline [

param_id, param

1, param a,

2, param b,

3, param c

];

in sheets, the [param] table would be as filter, and in charts like line charts the selected param is shown with: pick(param_id, param_a, param_b, param_c)

because I need to filter Parameter in filter, but also want to plot scatter plot with values from 2 different parameters as x and y, that's why such kind of data model and not use crosstable.

in both histogram and pivot table, I add addons to limit the number of values being displayed, but still it took forever to load.

My app is only 230mb, I am still making it, but in testing, it's taking too much time. Is there a way to just kill the session so the engine wont be calculating the stucked calculation? Or why pick() would cause such a slow performance? It was quite normal when all Parameters are in one field.

[rwunderlich]

Are you coding the pick() as a calculated dimension or in a variable?  Can you post the exact expression you are using?

-Rob

[luohda]

In Histogramm, normally I just select the REAL parameters in filters, and write: values. Now it's like: pick(param_id, param_a, param_b, param_c) on histogram.

In pivot table: original is: round(values, 0.01)
Now: round(
Pick(param_id, param_a, param_b,param_c)
,0.01)

My line charts and combo charts use also the same pick function and works quite fast, but this 2 not.

[luohda]

Histogram doesn't have dimension, the dimension in pivot table is time(every 15 mins). Each parameter, each 15 mins has a value (After filtering other related fields).

[lorenzoconforti]

Maybe the pick function is getting evaluated multiple times; does it work normally if you remove the pick function?

Try something like this to see if it gets better

$(=Pick(param_id, '[param_a]', '[param_b]','[param_c]')

[luohda]

in histogram it said invalid dimention when i use: $(=Pick(param_id, '[param_a]', '[param_b]','[param_c]')

[lorenzoconforti · Accepted solution]

I forgot the closing bracket

$(=Pick(param_id, '[param_a]','[param_b]','[param_c]'))

[luohda]

for the histogram it's still taking some time, but it works!! But I dont understand why turning it into string would work?

[lorenzoconforti · Accepted solution]

It works because dollar sign expansion is evaluated once (you can see that in the script editor where the expansion is evaluated straight away)

The way you did it, might have resulted to that function being calculated once per row, which could slow down significantly the chart