Same expresion in Text Box.

[sona_sa]

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.

[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?

[CELAMBARASAN]

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

[#4]

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