1 Reply Latest reply: Feb 8, 2015 10:45 AM by Elena Prandoni RSS

    Including only 1 dimension in expression


      Hello All,

       

      I have question on include/exclude dimensions in expression:

       

      My current expression is below, this calculates the aveerage of the gross margin by country in a text box. I want this expression to only update when a country field is selected. There are other fields in the table such as customer, manager, employee etc. but I want this expression to update only for Country selected. Please advise how this can be achieved.

       

       

      =num(IF(ProjectNumber<>'N/A',SUM({<ProjectNumber=>}
      (
      aggr(sum(if(USD_Net_Net_Amt>0,USD_Net_Net_Amt)),ProjectNumber)-num(aggr(sum(if(USD_STD_Cost>0,USD_STD_Cost)),ProjectNumber)))
      /
      aggr(sum(if(USD_Net_Net_Amt>0,USD_Net_Net_Amt)),ProjectNumber))
      /
      count(distinct IF(aggr(sum({<ProjectNumber=>}if(USD_Net_Net_Amt>0,USD_Net_Net_Amt)),ProjectNumber)<>0 and aggr(sum({<ProjectNumber=>}if(USD_STD_Cost>0,USD_STD_Cost)),ProjectNumber)<>0 ,ProjectNumber))
      ),'###,#0%')

       

      Thanks!