Skip to main content
Announcements
Qlik Launches Open Lakehouse and advanced agentic AI experience in Qlik Answers! | LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Tienemientje
Contributor

Count distinct with IF/Filter formula on measure

Hi,
I'm quite new to QS and I have a question on a calculation I want to make.

I want to count the number of distinct customers that got a value of a certain category.

Example data in my pivot:

Person Fee Other
An 100 10
An 350 0
Ben 0 100

 

So I want to count:

Fee Other
1 (person) 2

 

Can you help me identify a good formula for this case?

PS: I found a couple of similar cases, but either they were to complicated or the provided answer did not work in my case.

Labels (1)
3 Replies
E_Røse
Creator II

@Tienemientje , What did you try,  and how was it not working as you expected? With a 'certain category', do you mean within a given interval or a certain value?

Tienemientje
Contributor
Author

With the category, I mean like in my example "fee" and "other".  

I tried a number of variations on the following expression

Count (distinct [Name] and [Fees]>0)

It either gives an error, like if Y try to add the IF statement it says that it needs more parts. (which I can understand, similar to excel, but I found examples online where they did not seem to add the "if not" part).  Or it gives a zero or just some high number (either not the distinct values or not only those names for which the fee is above 0)

I now received something from a colleague, but I need to check further if it gives exactly the values I want

Count(distinct{<[name]={"=sum([fee]>0"}>}[name])

E_Røse
Creator II

Will

count({<Fee={">0"}>}distinct Person) and

count({<Other={">0"}>}distinct Person)

give you the numbers that you want, or do you somehow need to consider the sums per person? (asking because of your collegue's suggestion)