Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: COUNT DISTINCT WITH IF?

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 !

Employee
Employee

Re: COUNT DISTINCT WITH IF?

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