2 Replies Latest reply: Dec 2, 2014 8:08 PM by Jonathan Poole RSS

    COUNT DISTINCT WITH IF?

    Oded N

      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!

        • Re: COUNT DISTINCT WITH IF?
          Priyanka Rao

          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 !

          • Re: COUNT DISTINCT WITH IF?
            Jonathan Poole

            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