2
IF (A > 0, count (DISTINCT B))

If Variable A is greater than 0 then count variable B ignoring the duplicates with the use of distinct.

George Halford
  • 29
  • 7
  • 5
    Can you please show your whole query? The question is not clear in its current form. – Felix Pamittan Sep 03 '15 at 11:22
  • I want to create a query which sees whether variable a is greater than 0 and if it is then distinctly count variable B ignoring any duplicates how can I change my formula please? – George Halford Sep 03 '15 at 11:24
  • 1
    See, we can't see your screen so we don't have any idea what you're talking about. Provide sample data and expected result to clarify your question. – Felix Pamittan Sep 03 '15 at 11:26

3 Answers3

3

In sql:

count(distinct case when a>0 then b else null end)

In excel(using array with ctrl+shift+enter) assuming your data is found on columns A & B:

=SUM(IF(A:A>0,IF(FREQUENCY(B:B,B:B)>0,1),""))
Balinti
  • 1,524
  • 1
  • 11
  • 14
0

In Qlikview:

Normal expression:

=SUM(IF(A > 0,(DISTINCT B),0))

Unaffected by selections:

=Sum({1} IF(A > 0,(DISTINCT B),0))

SQL:

SELECT COUNT(DISTINCT CASE WHEN A > 0 THEN B ELSE NULL END) AS CountField
FROM yourtable
Matt
  • 14,906
  • 27
  • 99
  • 149
0

You could use the following:

=Count(DISTINCT IF (A > 0, B))

I would use 

=Count(DISTINCT {<A = {>0}>} B)

However the latter will ignore any selections on A

Shaun
  • 559
  • 1
  • 3
  • 17