Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to count certain values out of a column in a data set?

Hi everyone!

I hope you're all having a good day. I am trying to count the number of rebates, co-ops, and warranties in a column that is filled with other things too. I need them all separated. I want the box to look like the attached image with the purple filled in with numbers. My best attempt is something like this:

=Money(count(if({<FiscalYear={'2015'}>}rebates)))

It seems to me like I would need to do a count if, select the column, and do a where clause to get the data count out, but I don't know how to assemble that. Any help would be greatly appreciated! Thanks!

12 Replies
rubenmarin

Hi Elizabeth, if you're using set analisys you don't need the "if":

=Money(sum({<FiscalYear={'2015'}>}rebates))

Count() returns the number of records counted, you want to Sum() the values?

Not applicable
Author

I want to count the values. One of the values is wty for warranty, which you can't sum. Another value code is 2 for co-op, but if I sum those I will get double the number of occurrences. I tried your formula with count (by the way, I just noticed money shouldn't be there because it's a count), and it didn't work. I just got 0. Thanks for responding though! Any other ideas?

rubenmarin

Ok, so you should have something like:

Count({<FiscalYear={'2015'}>}rebates)

This will count the values on the field rebates with' 2015' selected in FiscalYear, can you add 2 list boxes with fields FiscalYear and rebates, select '2015' in FiscalYear and check if there are any possible values in rebates Field.

Not applicable
Author

All right, I think I'm just majorly mixed up, and I apologize for being so unclear! The file is an Excel file called "FakeGMR". The column is called "Order reason". In order reason, there are 30ish codes I want to extract 2, 3, and wty separately. So what I've modified it to be, thanks to your help, is:

=count({<FakeGMR={'Order reason'}>3})

But this is wrong on several levels. Any ideas? Thank you so much!

rubenmarin

Hi maybe with this:

Count({<[Order reason]={'2','3'}>} [Order reason])

This is filtering the field [Order reason] for the values '2' and '3', then is counting the values on that field (you can use other field to the count)

Not applicable
Author

Didn't work Thanks so much for your help! Still came back as 0.

rubenmarin

Can you upload the document with sample data to check?

ankit777
Specialist
Specialist

use

for 2

count({<[Order reason]={'2'}>}[Order reason])

for 3

count({<[Order reason]={'3'}>}[Order reason])

for wty

count({<[Order reason]={'wty'}>}[Order reason])

Anonymous
Not applicable
Author

=count({1<[FiscalYear] ={'2015'}, [OrderReason] = {'2'}>}OrderReason)   <---- if it corresponds to rebates, put this in the purple rebates box next to the the rebates label

=count({1<[FiscalYear] ={'2015'}, [OrderReason] = {'wty'}>}OrderReason) <----- this should work for warranty. Could be 'WTY', depends on what the value looks like exactly in your excel column.



Your dataset must be hooked up to the Master Calendar in order to properly calculate for FiscalYear = {'2015'}.