11 Replies Latest reply: Mar 11, 2016 1:13 PM by Deborah Pyykkonen RSS

    Number formating with Variable Not working

    Deborah Pyykkonen

      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:

      Capture.JPG

       

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