2 Replies Latest reply: Aug 15, 2011 7:41 AM by Jeroen Veldhoen RSS

    Multiple number formats in one expression

    Jeroen Veldhoen

      Hi,

       

      I am using a straight table with one dimension and multiple expressions. Depending on the value of the dimension, I would like to use a differen number format.

       

      The dimension is called [Tech Name] and the expressions look something like:

       

      if([Tech Name]= 43, sum(expr1),
      if([Tech Name]= 44, sum(expr2),

      if([Tech Name]= 45, sum(expr3),
      if([Tech Name]= 46, sum(expr4)

      ))))

       

      Now I would like to have integer values for 43, 44 and 46, and percentages for 44.

       

      Is this possible?

       

      if ([Tech Name]=43, sum({< EA.Z_Risk_Grade = {'H'}, eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},
          engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} eng_net_bill_value_PTD/1000)
      if ([Tech Name]=44,
          (sum({< eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} (eng_net_bill_value_PTD))
          /
          sum({< eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} (eng_time_wip_PTD))),
      if([Tech Name]=45,
          sum(
          aggr(
              if(sum({< eng_costcenter_key = {$(vCostCenter)}, hours_month= {'>=$(=Date(v46_date_from))<=$(=Date(vReportingDate))'},
                  engagement_max_end_date_empty_flag = {1}  >}
                  timeAmount) =0 ,1)       
          ,engagementId, [KPI name]))
      ,

       

      if( [Tech Name]=46,
          count({< CA.costcenterId= {$(vCostCenter)},CA.ass_start_month = {'$(=date($(vReportingDate)))'}, CA.status = {'Discontinued'}>} CA.assessmentId)
          +
          count({< EA.costcenterId= {$(vCostCenter)},EA.ass_start_month = {'$(=date($(vReportingDate)))'}, EA.status = {'Discontinued'}>} EA.assessmentId)
                 
      ))))

      if ([Tech Name]=43, sum({< EA.Z_Risk_Grade = {'H'}, eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},
          engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} eng_net_bill_value_PTD/1000)
      if ([Tech Name]=44,
          (sum({< eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} (eng_net_bill_value_PTD))
          /
          sum({< eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} (eng_time_wip_PTD))),
      if([Tech Name]=45,
          sum(
          aggr(
              if(sum({< eng_costcenter_key = {$(vCostCenter)}, hours_month= {'>=$(=Date(v46_date_from))<=$(=Date(vReportingDate))'},
                  engagement_max_end_date_empty_flag = {1}  >}
                  timeAmount) =0 ,1)       
          ,engagementId, [KPI name]))
      ,

       

      if( [Tech Name]=46,
          count({< CA.costcenterId= {$(vCostCenter)},CA.ass_start_month = {'$(=date($(vReportingDate)))'}, CA.status = {'Discontinued'}>} CA.assessmentId)
          +
          count({< EA.costcenterId= {$(vCostCenter)},EA.ass_start_month = {'$(=date($(vReportingDate)))'}, EA.status = {'Discontinued'}>} EA.assessmentId)
                 
      ))))

       

      Now