1 Reply Latest reply: Oct 5, 2011 7:00 PM by Naveen Ketha RSS

    filter in a chart expression not working

      For Ex: If I have data as below in the table

       

      MONTH, MEAL_TYPE, SPEND, EMPLOYEE_ID

      JAN,Lunch,20, 100

      JAN,Breakfast,20,101

      FEB,Dinner,20,102

      FEB,Dinner,4,103

      MAR,Lunch,20,102

      MAR,Lunch,3,103

       

      I would like get the total count of employees who had spent greater than 5 in each meal_type category for each month

       

      So I have MONTH and MEAL_TYPE as Dimensions in chart and in expression, I have formula as below.

       

      =If(SPEND> 5,Count (DISTINCT EMPLOYEE_ID),1)

       

       

      It's returning 1 always. If I just have Count (DISTINCT EMPLOYEE_ID), It's working , but I need results only where SPEND > 5

       

      I cannot filter at the Table level as I may need  all records in another chart.

       

      Please let me know how can i resolve the above issue.

       

      Thanks,

      Naveen.