Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- How can I click a measure with Valuelist() ?

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

simotrab

Creator III

2017-10-10
12:23 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How can I click a measure with Valuelist() ?

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.

- Tags:
- valuelist()

1 Solution

Accepted Solutions

swuehl

MVP

2017-11-09
05:57 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)

))

9 Replies

simotrab

Creator III

2017-11-09
05:53 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3,309 Views

swuehl

MVP

2017-11-09
05:57 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Creator III

2017-11-09
06:04 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

3,310 Views

swuehl

MVP

2017-11-09
06:08 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

3,310 Views

simotrab

Creator III

2017-11-09
06:17 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

3,310 Views

swuehl

MVP

2017-11-09
06:24 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

3,310 Views

simotrab

Creator III

2017-11-09
06:36 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3,310 Views

swuehl

MVP

2017-11-09
06:20 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Creator III

2017-11-10
02:42 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

3,310 Views

Community Browser