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: 
Not applicable

COUNT DISTINCT WITH IF?

Hello

Column 1: Meter ID 

Column 2: RequestStatusID (values:1,21,999)

Column 3: DateTime

same  MeterID and RequestStatusID can show several times but they are different on the DateTime field

I would like to count all the 1 values for UNIQUES MetersID for all datetime period

HOW SHOULD I USE THE DISTINCT COMMAND FOR THAT PURPOSE IN THAT SYNTAX BELOW?

count (if(RequestStatusID=1, RequestStatusID))

THANKS!

2 Replies
Not applicable
Author

Hi,

If your DateTime Field is unique, then Take it as a dimension in a Pivot Table and Count the Meter ID in the Expression.

Check the ExpressionTotal to get the Total.

Hope it Helps !

Cheers !

JonnyPoole
Employee
Employee

If the requestID =1 , then count the meterID, but only give a grand total of the unique meterIDs achieved through this way:


count ( distinct  if(RequestStatusID=1, MeterID))


Or with SET ANALYSIS


count( distinct  {$<RequestStatusID={1}>} MeterID)


the latter may perform slightly faster on larger data volumes