Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hammermill21
Contributor II

Counting Data and excluding multiple items

Hello,

I need to count how many inspections ([Bascom InspectionFrequency]) are monthly with distinct barcode numbers ([Bascom Barcode]) and do not have _R (300126_R) at the end of the number.

So I currently have this:

Count({<[Bascom InspectionFrequency]= 'MONTHLY', [Bascom Barcode]>} [Inventory Bascom Barcode])

But it's over counting and every time I try to exclude the barcodes numbers ending in _R I get an error.  Any ideas?

Thank you!

1 Solution

Accepted Solutions
MVP
MVP

Re: Counting Data and excluding multiple items

How about this

Count({<[Bascom InspectionFrequency] = {'MONTHLY'}, [Bascom Barcode] = e({<[Bascom Barcode] = {"*_R"}>})>} DISTINCT [Inventory Bascom Barcode])

7 Replies
jaumecf23
Contributor III

Re: Counting Data and excluding multiple items

Hi,

Maybe something like this:

Count({<[Bascom InspectionFrequency]= 'MONTHLY', [Bascom Barcode]-={"*_R (300126_R)"}>} [Inventory Bascom Barcode])

hammermill21
Contributor II

Re: Counting Data and excluding multiple items

Hi, nope that's not working. And I know it should be 458 because if I break it down in a table and exclude all that end on _R and all monthly I get the correct total:

Bascom.PNG

m_perreault
Contributor II

Re: Counting Data and excluding multiple items

Make sure you put 'MONTHLY'  in brackets

Count({<[Bascom InspectionFrequency]= {'MONTHLY'}, [Bascom Barcode]-={"*_R (300126_R)"}>} [Inventory Bascom Barcode])

hammermill21
Contributor II

Re: Counting Data and excluding multiple items

That's not working either. Maybe I should exclude all barcodes that end with _R in the script?

hammermill21
Contributor II

Re: Counting Data and excluding multiple items

So I've excluded anything ending with _R in the script and tried:

Count({<[Bascom InspectionFrequency]= {'MONTHLY'}, [Bascom Barcode]>} [Inventory Bascom Barcode])

And still getting the wrong count

MVP
MVP

Re: Counting Data and excluding multiple items

How about this

Count({<[Bascom InspectionFrequency] = {'MONTHLY'}, [Bascom Barcode] = e({<[Bascom Barcode] = {"*_R"}>})>} DISTINCT [Inventory Bascom Barcode])

hammermill21
Contributor II

Re: Counting Data and excluding multiple items

Hey SUnnt,

I was able to exclude the _R from the backend but I realized (thanks to you) that this is what I needed:

Count({<[Bascom InspectionFrequency]= {'MONTHLY'}, [Inventory Bascom Barcode]>} DISTINCT[Bascom Barcode])

I kept trying to put Distinct in the wrong place.

THANK YOU!!!