Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Tienemientje
Contributor
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
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
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
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)