11 Replies Latest reply: Jul 29, 2014 1:39 AM by Deepayan Chatterjee RSS

    Set Analysis - Count(distinct not working



      Goal : To calculated Promotional Order % to Total Orders



      Total orders  = count(distinct(order_number))

      Promotional Orders = count(DISTINCT(if(( promo_order_flag = 1),order_number,0)))



      Year: year_no

      (if I use:  if ( promo_order_flag=1, year_no)  Then I get correct count for Promotional orders, else I am getting unwanted blank rows





      To calculate Promotional Order %


      Now I changed expression of Total Orders = count( {$<promo_order_flag =, year_no=, day_no=, month_no=, redemption_type=>}distinct order_number)


      But still I am getting same count.



      If I remove promo_order_flag=1 from dimension (year_no): Then I get correct count, but getting unwanted rows



      Please suggest where I am doing wrong! OR How to modify statements?


      Many Thanks in Advance

      - Deep