Number formating with Variable Not working

[#1]

I created a variable to assign formats to each value based on the value ID. It works if I only select ONE component ID but when I have a group of ID's it fails and I will always have  a group.  I added the aggr function before and after min function and still does not work unless I select only one.  What am I missing?

vComponentFormat is defined as follows

=if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 1,'#,##0.;(#,##0.)',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 2, '#,##0%',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 3, '#,##0.0%',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 4, '#,##0.00;(#,##0.00)',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 5, '#,##0.00%',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 6, '#,##0.0;(#,##0.0)',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 7, '#,##0.000;(#,##0.000)'
)))))))

I tried this as well and same exact results as below.  Switched to using the ID instead of name field and removed Min().  I also tried it without the aggr() function.

if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 2, '#,##0%',
if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 3, '#,##0.0%',
if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 4, '#,##0.00;(#,##0.00)',
if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 5, '#,##0.00%',
if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 6, '#,##0.0;(#,##0.0)',
if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 7, '#,##0.000;(#,##0.000)'
)))))))

Expresssion Formula:

num(
max({<[Year/Month],CURRENT_YEAR_INDICATOR = {'Y'},CURR_IND,[Month No] = {1}>}[Component Value])
,'$(vComponentFormat)')

Results:

Message was edited by: Deborah Pyykkonen. Added scrambled version of app

[#2]

And, YES, I have it set to Expression Default in Numbers tab.

[Peter_Cammaert]

It's awfully difficult to figure out which one of the many things that could go wrong is causing this issue. Care to upload an example document that shows this behavior?

Complex expressions not working when more than one value is active usually points to one part of the expression returning multiple results instead of the expected single one. A  trick that may prove useful: can you break down your expression into its major parts, and display the result of these expression pieces along with the remainder of the object you want to fix? That way you can observe the behavior of - for instance - the aggr() function in the context of the (unknown) dimensions you are using.

Best,

Peter

[Digvijay_Singh]

"I created a variable to assign formats to each value based on the value ID."

Not sure but I am not able to understand where ID is being used in variable definition.

Also as we know Aggr function creates virtual table of values against defined dimension, as in your case -

aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 1

you are creating virtual table of minimum values of Component Format for each COMPONENT_NAME_RELATED_INFO

Not sure how it will work if you have multiple "COMPONENT_NAME_RELATED_INFO" , it may work if you have single COMPONENT_NAME_RELATED_INFO.

You may need Min outside Aggr so that left side of equation results to single value.

[#5]

I updated the expression to use the METRIC_COMPONENT_PK_ID instead of the name.  It is a 1:1 however.  But I get the same results.

[martinpohl]

I think it's a problem with "="

You mustn't use it in your variable, but have to in your expression ($(=vComponentFormat)

Regards

[#7]

Thank you Martin.  I tried that as well and no luck.

[#8]

I attached a scrambled version of the app.  with a couple differ variables I tried using.  You sill see that the chart above works perfectly with the vMetricFormat but there is always one metric code selected so it is not a problem.  The Components are always multiple and that appears to be the issue. 

[#9]

Thank you Digvijay.   I tried both inside and outside aggr() but no luck.  I must be missing something simple.  I have not used the aggr() function much.

[martinpohl · Accepted solution]

use this

=num(max({<[Year/Month],CURRENT_YEAR_INDICATOR = {'Y'},CURR_IND,[Month No] = {1}>}[Component Value]),$(=vTest1))

Regards