Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hammermill21
Creator III
Creator III

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
sunny_talwar

How about this

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

View solution in original post

7 Replies
jaumecf23
Creator III
Creator III

Hi,

Maybe something like this:

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

hammermill21
Creator III
Creator III
Author

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
Creator III
Creator III

Make sure you put 'MONTHLY'  in brackets

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

hammermill21
Creator III
Creator III
Author

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

hammermill21
Creator III
Creator III
Author

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

sunny_talwar

How about this

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

hammermill21
Creator III
Creator III
Author

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!!!