filter in a chart expression not working

Wed, 05 Oct 2011 22:00:23 GMT

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.

Wed, 05 Oct 2011 23:00:53 GMT

I could resolve it by the below formula

=((COUNT(DISTINCT( if([SPEND] > 5,[EMPLOYEE_ID])))))

Thanks,

Naveen

topic filter in a chart expression not working in QlikView