Expr

[smilingjohn]

Hi All,

I have a field called SPEND which has two strings in it Finance and Sales Iam using below expression in the PIE chart which dont have any dimensions ..this will display me the percentage value .. Can someone please tell me how do i include the DImensions value for only FInance in this expression ? this expression should always display the value for Finance from SPEND field .....

num(

(

sum(aggr(

if(

round(

(

(Sum({<MCERFlag = {1},>}CurrQtyReceived * CurrCostLocal))

-

(Sum({<MCERFlag = {1}>}CurrQtyReceived * PriorCostLocal))

)

-

(

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

-

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced)))

)

)

>0,

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,0)

,Entity_Item_Curr_MonthID,SpendCol))

)

/

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,'#,##0.00%')

)

)

Thanks in advance

[MK_QSL]

Add below as calculated dimension

IF(SPEND = 'Finance',SPEND)

Tick Suppress when value is null

[Anil_Babu_Samineni]

May be this?

num( ( sum(aggr( if( round( ( (Sum({<Spend = {Finance}>}CurrQtyReceived * CurrCostLocal))

-

(Sum({}CurrQtyReceived * PriorCostLocal)) ) - ( (Sum({<Spend = {Finance}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

-

(Sum({<Spend = {Finance}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced))) ) ) >0,

(Sum({<Spend = {Finance}>}CurrQtyReceived * CurrCostLocal)) ,0) ,Entity_Item_Curr_MonthID,SpendCol)) ) / (Sum({<Spend = {Finance}>}CurrQtyReceived * CurrCostLocal)) ,'#,##0.00%')

[smilingjohn]

Hi Anil,

This is showing error

[Anil_Babu_Samineni]

Looks like i missed it here. If below on is not helping can you show the error image where we getting?

num( ( sum(aggr( if( round( ( (Sum({<Spend = {'Finance'}>}CurrQtyReceived * CurrCostLocal))

-

(Sum({<Spend = {'Finance'}>}CurrQtyReceived * PriorCostLocal)) ) - ( (Sum({<Spend = {'Finance'}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

-

(Sum({<Spend = {'Finance'}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced))) ) ) >0,

(Sum({<Spend = {'Finance'}>}CurrQtyReceived * CurrCostLocal)) ,0) ,Entity_Item_Curr_MonthID,SpendCol)) ) / (Sum({<Spend = {'Finance'}>}CurrQtyReceived * CurrCostLocal)) ,'#,##0.00%')

[smilingjohn]

Hi Anil THis is the real Expression ...in which i have to include SPEND for finance and show the values permanenrtly

num(

(

sum(aggr(

if(

round(

(

(Sum({<MCERFlag = {1},>}CurrQtyReceived * CurrCostLocal))

-

(Sum({<MCERFlag = {1}>}CurrQtyReceived * PriorCostLocal))

)

-

(

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

-

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced)))

)

)

>0,

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,0)

,Entity_Item_Curr_MonthID,SpendCol))

)

/

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,'#,##0.00%')

)

)

[Anil_Babu_Samineni]

I would always check typo error first? Can you remove that?

num(

(

sum(aggr(

if(

round(

(

(Sum({<MCERFlag = {1},>}CurrQtyReceived * CurrCostLocal))

-

(Sum({<MCERFlag = {1}>}CurrQtyReceived * PriorCostLocal))

)

-

(

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

-

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced)))

)

)

>0,

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,0)

,Entity_Item_Curr_MonthID,SpendCol))

)

/

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,'#,##0.00%')

)

)

• Re: Expr

[smilingjohn]

I have removed it Anil ,

I added that comma only to add the (Sum({<MCERFlag = {1}>,SPEND={'Finance'} >}CurrQtyReceived * CurrCostLocal))

[smilingjohn]

HI Manish

This willl not work i want to display the value permamnenetly for Finance withought any dimension or calculated dimesion .....

[Anil_Babu_Samineni]

Again typo error

(Sum({<MCERFlag = {1}>,SPEND={'Finance'} >}CurrQtyReceived * CurrCostLocal))