Help needed to create an expression

[#1]

I am creating an expression that can give count of all employees who had spent > 5 for lunch/Breakfast and > 10 for dinner . I would  like to include only employees who are in city='NewYork'. I have my formula as below which works, but I also need to put a condition that will make the below calculation only for New York employees

=COUNT(DISTINCT( if(([SPEND] > 5 and Match(MEAL_TYPE,'Lunch','Breakfast'))

or ([SPEND] > 10 and Match(MEAL_TYPE,'Dinner'))

,[EMPLOYEE_ID])))

FYI. There are 2 tables A and B

A has below fields

MONTH,SPEND,MEALTYPE,EMPLOYEE_ID

B has below fields

EMPLOYEE_ID,CITY

I hope i need to use set analysis which i am trying to learn.Please help me resolve the above issue.

Thanks,

Naveen

[its_anandrjs]

Hi,

You have to write a code like below or just add city='NewYork' because it will connected with EMPLOYEE_ID i assume it if it is connected then the below code works better.

=COUNT(DISTINCT( if(([SPEND] > 5 and Match(MEAL_TYPE,'Lunch','Breakfast'))

or ([SPEND] > 10 and Match(MEAL_TYPE,'Dinner') and  CITY='NewYork' )

,[EMPLOYEE_ID])))

Let me know about this

HTH

Rgds

Anand

[qliksus]

Hi for set analysis use this

count( {  <spend={">5"},meal_type={'lunch','breakfast'},city={'new york'}>+ <spend={">10"},meal_type={'dinner'},city={'new york'}> } Employee id)

[#4]

Hi Anand,

The 2 tables are connected but below formula does not work. I am not sure if you can use fields from 2 different tables in the same expression for aggregation function

=COUNT(DISTINCT( if(([SPEND] > 5 and Match(MEAL_TYPE,'Lunch','Breakfast'))

or ([SPEND] > 10 and Match(MEAL_TYPE,'Dinner') and  CITY='NewYork' )

,[EMPLOYEE_ID])))

The above formula removes  employees who had spent on dinner and includes employess in other cities.

Thanks,

Naveen.

[#5]

Hi Qliksus,

the below expression does not give me any result.

count( {  <spend={">5"},meal_type={'lunch','breakfast'},city={'new york'}>+ <spend={">10"},meal_type={'dinner'},city={'new york'}> } Employee id)

The below expression

count( {  <spend={">5"},meal_type={'lunch','breakfast'}>+ <spend={">10"},meal_type={'dinner'},city={'new york'}> } Employee id)

behaves exactly like

=COUNT(DISTINCT( if(([SPEND] > 5 and Match(MEAL_TYPE,'Lunch','Breakfast'))

or ([SPEND] > 10 and Match(MEAL_TYPE,'Dinner') and  CITY='NewYork' )

,[EMPLOYEE_ID])))

Where it removes  employees who had spent on dinner and includes employess in other cities.

Thanks,

Naveen.