topic Help needed to create an expression in QlikView

Help needed to create an expression

Thu, 06 Oct 2011 01:51:07 GMT

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

Help needed to create an expression

its_anandrjs — Thu, 06 Oct 2011 04:43:04 GMT

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

Re: Help needed to create an expression

qliksus — Thu, 06 Oct 2011 05:47:06 GMT

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)

Help needed to create an expression

Thu, 06 Oct 2011 20:28:17 GMT

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.

Help needed to create an expression

Thu, 06 Oct 2011 21:00:26 GMT

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.