6 Replies Latest reply on Aug 31, 2016 11:10 AM by Mary Solbrig

    Count distinct on aggregated column

    Ajay Raman

      Hi

       

      I have a calculated field say CalcField1 as (SUM([Calc Automated1])/SUM([Unique Test Month Id]))*100

       

      I have another measure created as

      IF(INT([UniqueAutomated]) >=90)

      THEN

      "90-100"

      ELSEIF  (INT([UniqueAutomated]) >=75)

      THEN

      "75-89"

      ELSEIF  (INT([UniqueAutomated]) >=50)

      THEN

      "50-74"

      ELSEIF  (INT([UniqueAutomated]) >=25)

      THEN

      "25-49"

      ELSE

      "<25"

      END

       

      Now I create a report for each month for each of the row items which band they fall

       

      sample data

       

      Division     jan     feb     mar     apr     may

      abc    50-74     75-89     50-74      25-49     25-49        

      def     75-89     25-49     25-49     75-89     75-89

      ghi     50-74    50-74    25-49      90-100  75-89

       

      I need to create a report wiht the count of band for every month

      e.g for jan

      50-74 => Count 2

      75-89 => Count 1

       

      Thanks

      Ajay