Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

akumar_c
Contributor

Same expresion in Text Box.

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.

3 Replies
jason_michaelid
Honored Contributor II

Same expresion in Text Box.

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.

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

Not applicable

Same expresion in Text Box.

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

Community Browser