topic Re: Filter for a dynamic field in a pivot table in QlikView

Filter for a dynamic field in a pivot table

mrthomasshelby — Sat, 16 Nov 2024 18:32:02 GMT

Hi all!

I have a table titled Outlier Analysis as shown below:

In this table, I'm doing a simple Outlier Analysis for each of the lines. The logic to decide if a Quote price from one supplier is Outlier/OK is: if([Quote Price]>=(1+v_Outlier)*Avg_Quote,'Outlier',

if([Quote Price]<=(1-v_Outlier)*Avg_Quote,'Outlier','OK'))

where [Quote Price] is the price for that line from the supplier and Avg_Quote is the Avg of all the suppliers' Quote Prices, calculated from the script and v_Outlier is a variable which is a user input and can be changed from the slider on the left(after Quotes Scale text box).

Now what I want to have here is a filter or a button that should Exclude all the line items which are Outliers and show only the items which show up as 'OK' in the table. I have tried using the same logic in a List box expression but sadly it doesn't work. This has been made complex by the fact that the Outliers are dynamic and change according to the user input on the slider as I can't create a field to use as a filter in the script.

Any help is much appreciated. Attaching the app for your reference.

@sunny_talwar @tresB

Thanks in advance.

Re: Filter for a dynamic field in a pivot table

Brett_Bleess — Wed, 01 Jul 2020 11:52:47 GMT

I would point you to the following Design Blog posts which should put you on the right track:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Hopefully those help you move things along.

Regards,
Brett

Re: Filter for a dynamic field in a pivot table

sunny_talwar — Wed, 01 Jul 2020 12:51:37 GMT

I think to do this, you will need a field which is a combination of Baseline # and Supplier created in the script. This field can be as simple as this:

[Baseline #]&'|'&Supplier as [Baseline Supplier Key]