Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
joep_026
Contributor III
Contributor III

Filter a table by measure

Hello all,

I have recently written a post already about filtering a table by measure. The solution was not working, because you had to pay for an extension.

There should be a way this is possible, regarding this post: https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-filter-the-chart-by-Measure/m-p/43630/highlig...

Can anyone help me build a formula/ function that can do this. You  can see the data set in the picture.

Example: I want to be able to filter my table so I see either PCS, STOCK or MARGIN, instead of all 3 of the same time.

P.S. I cannot share/ open my QVF file, since I do not have the desktop application yet.

 

Kind regards,

Joep Schiffelers

 
Labels (2)
1 Solution

Accepted Solutions
Prashant_Naik
Partner
Partner

Hi Joe,

you can create a Inline table like this

Inlinetbl:

load * Inline [

   measurelist,measuresort

   PCS1,1

   STOCK1,2

   TURNOVER1,3

];

and in your pivot chart add this measurelist field in your column dimension.

and in your measure expression write the code as 

pick(match(measuresort,1,2,3),

    PCS1 expression, STOCK1 expression,TURNOVER1 expresssion

)

 Capture.PNG

Regards,

Prashant

 

View solution in original post

6 Replies
Prashant_Naik
Partner
Partner

Hi Joe,

you can create a Inline table like this

Inlinetbl:

load * Inline [

   measurelist,measuresort

   PCS1,1

   STOCK1,2

   TURNOVER1,3

];

and in your pivot chart add this measurelist field in your column dimension.

and in your measure expression write the code as 

pick(match(measuresort,1,2,3),

    PCS1 expression, STOCK1 expression,TURNOVER1 expresssion

)

 Capture.PNG

Regards,

Prashant

 

View solution in original post

joep_026
Contributor III
Contributor III
Author

Dear Prashant,

 

Thanks for your reply! The inline table loading worked, but the formula for the column inside the table doesn't seem to work. (See added picture)

 

Do you know how the formula can end up working?

 

Kind regards,

Joep

Prashant_Naik
Partner
Partner

Hi, 

I used that expression as an example, you need to replace the expression with your PCS1 expression and other also.

In your provided image you have taken 3 measures. The same measures you need to provide in expression.

Like...

Pick(match(measuresort,1,2,3),sum(PCS1),sum(Stock1),sum(turnover1))---- this expression to be written in single measure expression.

The example explains like this if measure is PCS1 then consider the expression as sum(PCS1) and same for other 2 measures to.

Regards,

Prashant

 

joep_026
Contributor III
Contributor III
Author

Alright,

So I did what you said, using the expressions. Now the formula is OK but it says ''invalid Dimension''

I'm doing something wrong still? See added pic.

Note: There is one fuction (Stock) that has multiple formulas within, adding and subtracting other values. That should'nt be a problem right?

I'm looking forward to your answer!

Prashant_Naik
Partner
Partner

Hi Joe,

I have attached a qvf file check the expression in that.

I have considered the same scenario that you have.

Regards,

Prashant

joep_026
Contributor III
Contributor III
Author

Hello Prashant,

 

Thanks for your time and solution!

I will check this out once I have access to the application.

 

Greetings,

Joep