Expression Issue

[#1]

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

[#2]

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')

[#3]

Hi Shweta,

  your question is not so clear.

can you send example what you want to say.

Regards

Vimlesh

[#4]

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' ))

[#5]

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.

[#6]

no

[sunny_talwar]

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')

-

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')

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,

S

[#8]

Hi,

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

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

Regards,

[#9]

my second xpression is correct

[#10]

Hi,

Just Remove the '=' From your variable and try