Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help needed to create an expression

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

4 Replies
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
Specialist II
Specialist II

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)

Not applicable
Author

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.

Not applicable
Author

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.