topic Same expresion in Text Box. in QlikView

Same expresion in Text Box.

sona_sa — Fri, 20 Apr 2012 04:29:00 GMT

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 — Fri, 20 Apr 2012 09:17:47 GMT

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 — Fri, 20 Apr 2012 10:14:45 GMT

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.

Fri, 20 Apr 2012 11:03:30 GMT

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