Expression Error

[NavinReddy]

Dear Experts Good Evening,

i have used two match conditions in my expression to get different decimals for specified fact number,

one match fiction working fine , the highlighted match function not working.

please suggest me i have tried different ways i didn't get any solution.

Many Thanks,

Niru

[NavinReddy]

For your reference below image is excel export for these metric im trying to adding for 3 months additional decimals.

Thanks,

Niranan

[rubenmarin · Accepted solution]

The format for 3 decimals should be:

if(Match(FCT_SETUP_ID,5795577,5805456,5805436,5805437,5805457,5794739),

if(EELE_TYPE_NM_SIGN='$',

if(ELE_VALUE_TYPE_CD = 'PCT',Num(MaxString({<Month=, MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}FCT_ACTL_VALUE_QT)*100,'##.000%'&'  ')&'',

if(ELE_VALUE_TYPE_CD = 'QTY','$'&Num(MaxString({<Month=, MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}FCT_ACTL_VALUE_QT),',###,###.000'))),

if(ELE_VALUE_TYPE_CD = 'PCT',Num(MaxString({<Month=, MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}FCT_ACTL_VALUE_QT)*100,'##.000%'&'  ')&'',

if(ELE_VALUE_TYPE_CD = 'QTY',Num(MaxString({<Month=, MonthYear={"$(=Date($(vMonth),'MMM YY'))"}>}FCT_ACTL_VALUE_QT),',###,###.000')))),

[NavinReddy]

For only If ELE_VALUE_TYPE_CD='PCT' --> 3 decimals mandatory, for QTY less priority

[NavinReddy]

Hi Ruben,Thank you very much its working fine sir,Many thanks

[NavinReddy]

Hi Ruben,

suggest me if you want i will create new threat for this. i have one issue with Tier3 Benchmark,i have implemented to insert $ symbol for some of metrics,if can i use one $ for expression its not working as expected.i need use two $$ symbols its happing for only Tier3 bench mark.

Many Thanks,

Niranjan

[rubenmarin]

Hi Niranjan, adding the simbl as string is not the correct way to do this, instead of:

'$'&'$'&num(Num(GREEN_RAG),'###,###.0'))),

Try setting using number format:

=if(EELE_TYPE_NM_SIGN='$',

aggr(if(IsNull(HIGH_GREEN_RAG),

if(ELE_VALUE_TYPE_CD = 'PCT',Num(GREEN_RAG,'##.00%'&'  ')&'',

if(ELE_VALUE_TYPE_CD = 'QTY',num(Num(GREEN_RAG),'$###,###.0'))),

if(ELE_VALUE_TYPE_CD = 'PCT', Num(HIGH_GREEN_RAG*100,'##.00%'&'  ')&' '&' '&Num(Num(GREEN_RAG*100),'##.00%'&'  ')&'',

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG,'$###,###.0')&''&' '&Num(GREEN_RAG,'$###,###.0')))),FCT_SETUP_ID,Metric,PROC_NM,ELE_TYPE_NM,BUS_ORG_NM1,OWNER_NM),

aggr(if(isnull(HIGH_GREEN_RAG),

if(ELE_VALUE_TYPE_CD = 'PCT',Num(GREEN_RAG,'##.00%'&'  ')&'',

if(ELE_VALUE_TYPE_CD = 'QTY',Num(GREEN_RAG,'$###,###.0'))),

if(ELE_VALUE_TYPE_CD = 'PCT', Num(HIGH_GREEN_RAG*100,'##.00%'&'  ')&' '&' '&Num(Num(GREEN_RAG*100),'##.00%'&'  ')&'',

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG,'$###,###.0')&' '&Num(GREEN_RAG,'$###,###.0')))),FCT_SETUP_ID,Metric,PROC_NM,ELE_TYPE_NM,BUS_ORG_NM1,OWNER_NM))

[NavinReddy]

Hi Ruben,

some of number format fact ids also im getting $ symbol

[rubenmarin]

In that case remove the '$' from the Else part of the first If():

=if(EELE_TYPE_NM_SIGN='$',

aggr(if(IsNull(HIGH_GREEN_RAG),

if(ELE_VALUE_TYPE_CD = 'PCT',Num(GREEN_RAG,'##.00%'&'  ')&'',

if(ELE_VALUE_TYPE_CD = 'QTY',num(Num(GREEN_RAG),'$###,###.0'))),

if(ELE_VALUE_TYPE_CD = 'PCT', Num(HIGH_GREEN_RAG*100,'##.00%'&'  ')&' '&' '&Num(Num(GREEN_RAG*100),'##.00%'&'  ')&'',

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG,'$###,###.0')&''&' '&Num(GREEN_RAG,'$###,###.0')))),FCT_SETUP_ID,Metric,PROC_NM,ELE_TYPE_NM,BUS_ORG_NM1,OWNER_NM),

aggr(if(isnull(HIGH_GREEN_RAG),

if(ELE_VALUE_TYPE_CD = 'PCT',Num(GREEN_RAG,'##.00%'&'  ')&'',

if(ELE_VALUE_TYPE_CD = 'QTY',Num(GREEN_RAG,'###,###.0'))),

if(ELE_VALUE_TYPE_CD = 'PCT', Num(HIGH_GREEN_RAG*100,'##.00%'&'  ')&' '&' '&Num(Num(GREEN_RAG*100),'##.00%'&'  ')&'',

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG,'###,###.0')&' '&Num(GREEN_RAG,'###,###.0')))),FCT_SETUP_ID,Metric,PROC_NM,ELE_TYPE_NM,BUS_ORG_NM1,OWNER_NM))

[NavinReddy]

Hi Ruben,

Still im facing same issue can you please any another way to remove.

Many Thanks,

Niranjan

[NavinReddy]

Hi Ruben,

Good Morning,

i have tried with your expression still i m getting the same error.

is there any other way we can able to add $ symbol

Best Regards,

Niranjan