Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Trying to use the WHERE clause to filter on a variable

Hello,

I am asking for help loading REPORTING_PRINTER counts into my table.  I only want to display the cases where the count is greater than 25.

Here is the expression I am using, but it is not working:

=Count({<Count[REPORTING_PRINTERS]={">=25"}>}(REPORTING_PRINTERS))

Thank you.

1 Solution

Accepted Solutions
MVP
MVP

Re: Trying to use the WHERE clause to filter on a variable

I am not sure why you still see a zero, but when I create the app with the sample data you provided above, I am not seeing any issues

Capture.PNG

May be you can share your qvf file for us to take a look at?

11 Replies
MVP
MVP

Re: Trying to use the WHERE clause to filter on a variable

Maybe something like

=Count({< CASES = {"=Count([REPORTING_PRINTERS]) >=25"} >} [REPORTING_PRINTERS] )


Where CASES is the field name that you actually want to filter by the printer count.

MVP
MVP

Re: Trying to use the WHERE clause to filter on a variable

May be this

Count({<Cases = {"=Count(REPORTING_PRINTERS) >= 25"}>}REPORTING_PRINTERS)

Since you mentioned that you want to display cases when reporting_printer count is over 25, I assumed the cases is a field in your dashboard, if it is called something else, please change it accordingly

Not applicable

Re: Trying to use the WHERE clause to filter on a variable

Hello Sunny,

Sorry my post was confusing, maybe I should have said "instances", not "cases"..

To be more specific, there are some Cities with Printer Counts below 25, I do not want to display those..

Right now I have:

City       Printer Count

A           300

B           15

C           200

I would like instead:

City      Printer Count

A          300

C          200

Thank you.

Not applicable

Re: Trying to use the WHERE clause to filter on a variable

Hello Stefan,

Sorry my post was confusing, maybe I should have said "instances", not "cases"..

To be more specific, there are some Cities with Printer Counts below 25, I do not want to display those..

Right now I have:

City       Printer Count

A           300

B           15

C           200

I would like instead:

City      Printer Count

A          300

C          200

Thank you.

Not applicable

Re: Trying to use the WHERE clause to filter on a variable

Hello Stefan,

I used your example and created:

Count({<Place_Name = {"=Count([REPORTING_PRINTERS])>=25"}>}[REPORTING_PRINTERS])

But I know have to figure out how to remove the zeros.  I went from this:

City     Printer Count

A         300

B         15

C         200

To this:

City     Printer Count

A         300

B         0

C         200

I clicked un-clicked "Include zero values" under Data handling, but that did not solve it.  Thank you.

Not applicable

Re: Trying to use the WHERE clause to filter on a variable

Hello Sunny,

I used your example and created:

Count({<Place_Name = {"=Count([REPORTING_PRINTERS])>=25"}>}[REPORTING_PRINTERS])

But I know have to figure out how to remove the zeros.  I went from this:

City     Printer Count

A         300

B         15

C         200

To this:

City     Printer Count

A         300

B         0

C         200

I un-clicked "Include zero values" under Data handling, but that did not solve it.  Thank you.

MVP
MVP

Re: Trying to use the WHERE clause to filter on a variable

I am not sure why you still see a zero, but when I create the app with the sample data you provided above, I am not seeing any issues

Capture.PNG

May be you can share your qvf file for us to take a look at?

hemeshreddy
New Contributor III

Re: Trying to use the WHERE clause to filter on a variable

or you can use if statement

if(sum([Printer count])>=25,sum([Printer count]))

pathiqvd
Contributor III

Re: Trying to use the WHERE clause to filter on a variable

Hi,

try like this,

=Sum({<City={"=sum(PrinterCount)>25"}>}PrinterCount)

Regards,