topic Re: Expression Issue in QlikView

Expression Issue

Fri, 20 Feb 2015 10:11:19 GMT

Hi all,

I have to subtract a value of Aero income (which is present inside the Particulars field with the Less :Collection charges in UDF (which i present inside DIAL MIS Grouping for expense column. For this i used following expression,

if(Particulars='Aero Income' ,num(vAeroIncomeMTD,'#,##0.00'),

num(fabs(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'} >}AmountInLC_Actual_PNL)

/10000000),'#,##0.00' ))

=num(num(fabs(Aggr(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'},Particulars={'Aero Income'} >}AmountInLC_Actual_PNL),Particulars)

/10000000),'#,##0.00')

num(fabs(Aggr(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'},[DIAL MIS Grouping (for ExpenseDB)]={'Less: Collection Charges on UDF '} >}AmountInLC_Actual_PNL),Particulars)

/10000000),'#,##0.00'),'#,##0.00')

it gives the correct result when i added only Particulars as a dimension i.e. 252 ,but if I add DIAL MIS Grouping for expense and expand it it gives 252 in all the values for DIAL MIS Grouping for expense which is incorrect. as per logic value 252 should be divided for divided into different values of DIAL MIS Grouping for expense

Re: Expression Issue

Fri, 20 Feb 2015 10:12:25 GMT

if(Particulars='Aero Income' ,num(vAeroIncomeMTD,'#,##0.00'),

num(fabs(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'} >}AmountInLC_Actual_PNL)

/10000000),'#,##0.00' ))

where vAeroIncomeMTD:-

=num(num(fabs((sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'},Particulars={'Aero Income'} >}AmountInLC_Actual_PNL))

/10000000),'#,##0.00')

num(fabs((sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'},[DIAL MIS Grouping (for ExpenseDB)]={'Less: Collection Charges on UDF '} >}AmountInLC_Actual_PNL))

/10000000),'#,##0.00'),'#,##0.00')

Re: Expression Issue

Fri, 20 Feb 2015 10:18:35 GMT

Hi Shweta,

your question is not so clear.

can you send example what you want to say.

Regards

Vimlesh

Re: Expression Issue

Fri, 20 Feb 2015 10:28:18 GMT

hi,

You want like this......

if(Particulars='Aero Income' ,

num(((fabs((sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'},Particulars={'Aero Income'} >}AmountInLC_Actual_PNL))

))-

(fabs((sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'},[DIAL MIS Grouping (for ExpenseDB)]={'Less: Collection Charges on UDF '} >}AmountInLC_Actual_PNL))

)))/10000000,'#,##0.00'),

num(fabs(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'} >}AmountInLC_Actual_PNL)

/10000000),'#,##0.00' ))

Re: Expression Issue

Fri, 20 Feb 2015 10:45:35 GMT

for example value of aero income is 252.86 and value of less :collectionn charges on udf is 0.54

so ,in my chart i want to show aeroincome as 252.86-0.54=252.32.

when i create a pivot between particulars and actual,it works fine

Particulars	Actual
Aero Income	252.32

but if added one more dimension DIAL MIS ... and expandthe pivot table it gives

Particulars DIAL MIS Actual

Aero Income Baggage 252.32.

Aero Income Counter 252.32.

Aero Income Landing 252.32.

Aero Income Parking 252.32.

Re: Expression Issue

Fri, 20 Feb 2015 11:35:47 GMT

Re: Expression Issue

sunny_talwar — Fri, 20 Feb 2015 11:54:58 GMT

Is your variable (vAeroIncomeMTD) using the Aggr function (like your initial post):

=num(num(fabs(Aggr(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'},Particulars={'Aero Income'} >}AmountInLC_Actual_PNL),Particulars)

/10000000),'#,##0.00')

/10000000),'#,##0.00'),'#,##0.00')

Or is it this?

=num(num(fabs((sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'},Particulars={'Aero Income'} >}AmountInLC_Actual_PNL))

/10000000),'#,##0.00')

num(fabs((sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'},[DIAL MIS Grouping (for ExpenseDB)]={'Less: Collection Charges on UDF '} >}AmountInLC_Actual_PNL))

/10000000),'#,##0.00'),'#,##0.00')

Best,

Re: Expression Issue

Fri, 20 Feb 2015 11:57:10 GMT

Hi,

Can you remove the '=' sign from the variable expression and try.

and if its not working then try num($(vAeroIncomeMTD),'#,##0.00')

Regards,

Re: Expression Issue

Fri, 20 Feb 2015 11:59:34 GMT

my second xpression is correct

Re: Expression Issue

Fri, 20 Feb 2015 12:01:11 GMT

Hi,

Just Remove the '=' From your variable and try