COUNT DISTINCT WITH IF?

[#1]

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]

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]

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