How can I click a measure with Valuelist() ?

[simotrab]

Hi community,

I'm studying the valuelist() function:

ValueList - chart function ‒ Qlik Sense

But I've a problem. After creating a table with that function, I cannot use the measures created as row of the dimension as a dimension occurrencies, i.e. using it as filters for example.

What I'd like to have, it is a simple table with all the measures I want used as occurencies of a dimension and, selecting each "measure", see only it on a barplot.

So I decided to create a table with all the measures I want, a barplot with them, using the nice valuelist(). But it is not working.

Here my data:

mov:

Load*Inline

[workerid,sale,type,year

1,5,a,2016

1,20,a,2016

2,1,a,2016

2,8,a,2017

3,9,a,2016

3,2,a,2017

3,1,a,2017

4,4,a,2017

];

wor:

Load* Inline

[worker,workerid

a,1

b,2

c,3

d,4

e,7

q,9

]

where Exists (workerid);

Here my functions with valuelist in the table that is going to be the filter:

dimension: =valuelist('Sum','Sum/TOTAL')

measure:

if(

   valuelist('Sum','Sum/TOTAL')='Sum'

   ,Sum(sale)

  ,if(valuelist('Sum','Sum/TOTAL') ='Sum/TOTAL'

   ,Sum(sale)/Sum(TOTAL {$<worker=>}sale)

))

I've tried to slap in a aggr(), but it does not work and also it makes not sense to me, but maybe I'm wrong.

The same in a barplot.

What I'm doing wrong? Thanks in advance for your time, and the app is attached.

EDIT: those are example data, I'd like to have a general rule without creating problem in any ER model.

EDIT: app refreshed with the Stefan's solution.

[simotrab]

Till now, it is not possible. If you're going to find the answer, please let me know.

[swuehl · Accepted solution]

Try replacing your valuelist() with a data island table:

MeasureTable:

LOAD * INLINE [

Measure

Sum

Sum/TOTAL

];

Now use Measure as dimension and as expression

if(

   Measure = 'Sum'

   ,Sum(sale)

  ,if(Measure ='Sum/TOTAL'

   ,Sum(sale)/Sum(TOTAL {$<worker=>}sale)

))

[simotrab]

Hi Stefan,

this is useful for this example (thanks), but it could not be a general rule: this means that in another ER, could imply problems in relationships within different table.

My bad, I have not specified that these are examples data, and I'd like to have a general rule.

[swuehl]

Hi Simone,

where exactely do you see problems in your data model?

The MeasureTable should be a data island table, not linked to any other table. If the Measure field name conflicts with other field name, creating an unwanted key, rename the Measure field name in the MeasureTable to any other name you like.

Basically, you can only filter on table dimensions based on a field in your data model, this is why you can't filter on valuelist() synthetic dimensional values.

[simotrab]

The problem is exactly the definition of island, i.e. the fact that is not connect to other entities in the data model.

In the example, I could not choose a worker to have his/her partial results in the measures used as dimension with an island.

I'd really like ti have the result of a valuelist(), measures as dimension, whom could be influenced by other dimension. In addiction, I'd like to use as "filters" the measure-dimension created by the hypotetically valuelist(), for further analysis.

The question is rather tricky, because the real meaning is that I do not want it working using whatever medium, I'd like to know if it works, and I could accept a workaround like your, but it should preserve the features of the valuelist() way.

[swuehl]

How does the Valuelist() behave differently from the model island field with regard to grouping / filtering your data, e.g. using other fields of your models as further dimensions?

[simotrab]

I've tried your solution, and it does not work, unless you add an aggr() (If I have read it well, if not, my bad).

=aggr(if(

   Measure = 'Sum'

   ,Sum(sale)

  ,if(Measure ='Sum/TOTAL'

   ,Sum(sale)/Sum(TOTAL {$<worker=>}sale)

)),Measure)

However the point is that if you choose a worker, the result of the previous formula is not affected by the selection, becoming a dimension. The valuelist() makes the measures as dimensions, and the results of the measure-dimension affected by the filters as in the example, refreshed with your solution (added at the question).

[swuehl]

In your sample app, you haven't used Measure as dimension and the expression I've posted above.

Doing so, it seems to work the same way as the valueslist, except that you can select the measure value.

Can you point me to the exact issue you are seeing (maybe with a screenshot) using the attached updated sample app?

[simotrab]

Now it's clear. Thanks a lot. I've not understand to use Measure as dimension and the expression as measure, but to use the measure as dimension.