3 Replies Latest reply: Apr 20, 2012 7:03 AM by Ashutosh Paliwal RSS

    Same expresion in Text Box.

    iTree Consulting

      Hi,

       

      Same expression are working on Bar Chart, Where I am calculating Country wise Salary. Now I want to claculate same expression in Text Box for total sum of salary for all countries in single text box.

       

      =(IF(Country_Empmstr_Map = 'India',
      Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
      ,(
      [Annual Sal_ANSALARY_Grade]*VCurrency_INR)/vCr)),

      IF(Country_Empmstr_Map = 'Australia',
      Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
      ,(
      [Annual Sal_ANSALARY_Grade]*VCurrency_AUD)/vCr)),

      IF(Country_Empmstr_Map = 'USA',
      Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
      ,(
      [Annual Sal_ANSALARY_Grade]*VCurrency_USD)/vCr)),

      IF(Country_Empmstr_Map = 'China',
      Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
      ,(
      [Annual Sal_ANSALARY_Grade]*VCurrency_CNY)/vCr)),

      IF(Country_Empmstr_Map = 'Denmark',
      Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
      ,(
      [Annual Sal_ANSALARY_Grade]*VCurrency_DKK)/vCr)),

      IF(Country_Empmstr_Map = 'Portugal' or Country_Empmstr_Map = 'Germany' or Country_Empmstr_Map = 'Netherlands',
      Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
      ,(
      [Annual Sal_ANSALARY_Grade]*VCurrency_EUR)/vCr))

      )))))))

       

      Thanks in advance.

        • Same expresion in Text Box.
          Jason Michaelides

          Your if statements are referring to countries as if they were dimension values in a chart (ie one on each line) but a text box doesn't have the concept of dimensions.  What exactly are you trying to achieve, a single number (all salaries from all countries summed together) or a list of countries and their TOTAL salary?

          • Same expresion in Text Box.
            Celambarasan Adhimulam

            Hi,

                 Check with Aggr function

                 Sum(

                 Aggr(

                

            (IF(Country_Empmstr_Map = 'India',
            Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
            ,(
            [Annual Sal_ANSALARY_Grade]*VCurrency_INR)/vCr)),

            IF(Country_Empmstr_Map = 'Australia',
            Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
            ,(
            [Annual Sal_ANSALARY_Grade]*VCurrency_AUD)/vCr)),

            IF(Country_Empmstr_Map = 'USA',
            Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
            ,(
            [Annual Sal_ANSALARY_Grade]*VCurrency_USD)/vCr)),

            IF(Country_Empmstr_Map = 'China',
            Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
            ,(
            [Annual Sal_ANSALARY_Grade]*VCurrency_CNY)/vCr)),

            IF(Country_Empmstr_Map = 'Denmark',
            Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
            ,(
            [Annual Sal_ANSALARY_Grade]*VCurrency_DKK)/vCr)),

            IF(Country_Empmstr_Map = 'Portugal' or Country_Empmstr_Map = 'Germany' or Country_Empmstr_Map = 'Netherlands',
            Sum(if(EMPLSTATUS_MasterData = '3' and VALIDFROM_QEMP <= date(today(),'YYYY-MM-DD') and VALIDTO_QEMP >= date(today(),'YYYY-MM-DD')
            ,(
            [Annual Sal_ANSALARY_Grade]*VCurrency_EUR)/vCr))

            )))))))

            ,Country_Empmstr_Map)

            )

             

            I didn't look in to your logic just added aggr and sum function.

             

            Celambarasan

            • Same expresion in Text Box.
              Ashutosh Paliwal

              Hi,

              If you want a total of all the sums in one table box then you can write it like

               

               

              Sum({<

              Country_Empmstr_Map = {'India'},

              EMPLSTATUS_MasterData = {3},

              VALIDFROM_QEMP = { "= <= date(today(),'YYYY-MM-DD')"}, VALIDTO_QEMP = {"= >= date(today(),'YYYY-MM-DD') "}
              }> (
              [Annual Sal_ANSALARY_Grade]*VCurrency_INR)/vCr))

               

               

              +

               

              Sum({<

              Country_Empmstr_Map = {'Australia'},

              EMPLSTATUS_MasterData = {3},

              VALIDFROM_QEMP = { "= <= date(today(),'YYYY-MM-DD')"}, VALIDTO_QEMP = {"= >= date(today(),'YYYY-MM-DD') "}
              }> (
              [Annual Sal_ANSALARY_Grade]*VCurrency_AUD)/vCr))

               

              +

              ....same way for other countries data...

               

              if, you want to show them in different text box for different countries then just take these expressions one by one and put in the different text boxes.

               

              You can optimize the expression by putting the currency in a field and linking it with country and that way you will not need to use this many expressions and only 1 expression will work.

               

              ..

              Ashutosh