topic Re: Building a filter off an if statement in QlikView

Building a filter off an if statement

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm looking to add a filter at the front of my dashboard to filter the underlying data and tables to show certain figures if a number is equal or great to 1, and the another set if it's less than 1.

I started with the below in a simple listbox and I was hoping it would offer two options (Y & N) that would filter the data down accordingly. I would need the calculation to be done at the entity level (one of the field within my data).

if(SUM(X)>=1,"Y","N")

Thanks.

Re: Building a filter off an if statement

sunny_talwar — Thu, 08 Dec 2016 17:31:50 GMT

Sum(X) based on what dimension?? You need to add a dimension here:

Aggr(If(Sum(X) >= 1, 'Y', 'N'), List Your Dimension/s Here)

Re: Building a filter off an if statement

Thu, 08 Dec 2016 17:56:19 GMT

Thanks Sunny. I simplified my query a bit already but within X I have my dimensions listed. This gives me a value at the entity_level and I'm looking to filter my dashboard to either show for those with value 0-1 or above 1

Re: Building a filter off an if statement

sunny_talwar — Thu, 08 Dec 2016 18:12:00 GMT

May be you want this?

If(X >= 1, 'Y', 'N')

Re: Building a filter off an if statement

Fri, 09 Dec 2016 09:24:28 GMT

Thanks Sunny, that does seem to work. Is there a way to quickly filter for 'Y' or 'N'? At the moment I need to drag and select and as I have multiple thousand values on my dimension, it's taking some time and is not always guaranteed to grab all. I have sorted the Y and N alphabetically to help.

Re: Building a filter off an if statement

sunny_talwar — Fri, 09 Dec 2016 11:55:08 GMT

Create a list box expression like this

If(X >= 1, 'Y', 'N')

Once you do this, you should be able to select Y or N to select there corresponding rows.