4 Replies Latest reply: Oct 6, 2011 5:00 PM by Naveen Ketha RSS

    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

        • Help needed to create an expression
          Anand Chouhan

          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
            susant Kumar swain

            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

                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.

              • Help needed to create an expression

                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.