Filter a table by measure

[joep_026]

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

[Prashant_Naik · Accepted solution]

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

)

 

Regards,

Prashant

[joep_026]

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]

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]

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]

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]

Hello Prashant,

Thanks for your time and solution!

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

Greetings,

Joep