Nested IFs

[#1]

Hi,

Fist post so apologies if this is in the wrong place.

I have been searching for the error in this expression for far too long and I am going around in circles.

Can anybody please help?

=IF(OVERALL_LEVEL_2_GROUP = 'COMBAT AIR',
  NUM((COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) /
  COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<MAI_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) + COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<MAI_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) + COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY)) * 100, '###0.00',

IF(OVERALL_LEVEL_2_GROUP = 'IOPS',
      NUM(COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) /
  COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<MAI_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) + COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<MAI_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) + COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY)) * 100, '###0.00', 

IF(OVERALL_LEVEL_2_GROUP = 'SM - DIRECT',
   NUM(COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}, SM_CHECK = {1} >} DISTINCT SM_PURCHASE_ORDER_KEY) /
   COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) + COUNT({$<SMSCMC_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) + COUNT({$<SM_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY))* 100, '###0.00', 

IF(OVERALL_LEVEL_2_GROUP = 'SM - PAN',
   NUM((COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) /
   COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) + COUNT({$<SMSCMC_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}, SM_CHECK = {1} >} DISTINCT SM_PURCHASE_ORDER_KEY) + COUNT({$<SM_NO_FORECAST_COVER_FLAG = {1}, SM_CHECK = {1} >} DISTINCT SM_PURCHASE_ORDER_KEY))* 100, '###0.00', 

IF(OVERALL_LEVEL_2_GROUP = 'Unallocated',
    NUM(COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<RECORD_COUNTER = {1}, MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) /
  COUNT({$<DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<DISTINCT MAI_PURCHASE_ORDER_KEY)) * 100, '###0.00', 

IF(OVERALL_LEVEL_2_GROUP = 'Supply Chain',
   NUM(COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) /
  COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<MAI_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) + COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<MAI_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) + COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY)) * 100, '###0.00',

  NUM(COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) /
  COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) + COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY)) * 100, '###0.00'))))))

Thank You

[sinanozdemir · Accepted solution]

Hi,

The problem is that you are not closing your Num functions. Please see below:

This is your original code:

Here is the corrected version:

I would highly suggest you to indent your code as you write along.

I am attaching my version. Let me know if it works.

Thanks

[#3]

Hi,

Many Thanks, I was very close to solving this before your advice however mine was more guess work than logic.  I've amended as suggested and added the necessary additional brackets after the Num's but something is still not quite right.

=IF(OVERALL_LEVEL_2_GROUP = 'COMBAT AIR',

  NUM(

(

  COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) /

    COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY)

) * 100, '###0.00'),

IF(OVERALL_LEVEL_2_GROUP = 'IOPS',

  NUM(

  (

  COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) /

    COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY)

) * 100, '###0.00'),

IF(OVERALL_LEVEL_2_GROUP = 'SM - DIRECT',

  NUM(

  (

  COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}, SM_CHECK = {1} >} DISTINCT SM_PURCHASE_ORDER_KEY) /

     COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) +

  COUNT({$<SMSCMC_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) +

  COUNT({$<SM_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY)

)* 100, '###0.00'),

IF(OVERALL_LEVEL_2_GROUP = 'SM - PAN',

NUM(

(

  COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) /

     COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) +

  COUNT({$<SMSCMC_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}, SM_CHECK = {1} >} DISTINCT SM_PURCHASE_ORDER_KEY) +

  COUNT({$<SM_NO_FORECAST_COVER_FLAG = {1}, SM_CHECK = {1} >} DISTINCT SM_PURCHASE_ORDER_KEY)

)* 100, '###0.00'),

IF(OVERALL_LEVEL_2_GROUP = 'Unallocated',

NUM(

(

  COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<RECORD_COUNTER = {1}, MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) /

    COUNT({$<DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<DISTINCT MAI_PURCHASE_ORDER_KEY)

) * 100, '###0.00'),

IF(OVERALL_LEVEL_2_GROUP = 'Supply Chain',

NUM(

(

  COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) /

    COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY)

) * 100, '###0.00'),

NUM(

(

  COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) /

    COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY)

) * 100, '###0.00')

))))))

[sinanozdemir]

Are you getting any errors or do you think the calculation is just off?

It seems like IF statements are closed too so my guess is that your condition may not be returning the accurate values,

IF(OVERALL_LEVEL_2_GROUP = 'Supply Chain', maybe this is not producing the exact match?

Try to use Wildmatch(OVERALL_LEVEL_2_GROUP, 'Supply Cha*').

Another thing I realize is that the line in red, what is the order of the operations? You are summing two values and then inserting divisor without a bracket.

NUM(

(

  COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) /

    COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY)

) * 100, '###0.00')

[#5]

Morning,

Sorry for delay.

There will be no failures so it won't be that.  My error remains as a 'Error in set modifier expression'

I have however takem into account what you say above with regards the order of the calculation and contained each part as would be necessary so I'm now left with the following:

=IF(OVERALL_LEVEL_2_GROUP = 'COMBAT AIR',

  NUM(

(

  (COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY)) /

    (COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY))

) * 100, '###0.00'),

IF(OVERALL_LEVEL_2_GROUP = 'IOPS',

  NUM(

  (

  (COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY)) /

    (COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY))

) * 100, '###0.00'),

IF(OVERALL_LEVEL_2_GROUP = 'SM - DIRECT',

  NUM(

  (

  (COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}, SM_CHECK = {1} >} DISTINCT SM_PURCHASE_ORDER_KEY)) /

     (COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) +

  COUNT({$<SMSCMC_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) +

  COUNT({$<SM_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY))

)* 100, '###0.00'),

IF(OVERALL_LEVEL_2_GROUP = 'SM - PAN',

NUM(

(

  (COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY)) /

     (COUNT({$<SMSCMC_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT SM_PURCHASE_ORDER_KEY) +

  COUNT({$<SMSCMC_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}, SM_CHECK = {1} >} DISTINCT SM_PURCHASE_ORDER_KEY) +

  COUNT({$<SM_NO_FORECAST_COVER_FLAG = {1}, SM_CHECK = {1} >} DISTINCT SM_PURCHASE_ORDER_KEY))

)* 100, '###0.00'),

IF(OVERALL_LEVEL_2_GROUP = 'Unallocated',

NUM(

(

  (COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<RECORD_COUNTER = {1}, MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY)) /

    (COUNT({$<DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<DISTINCT MAI_PURCHASE_ORDER_KEY))

) * 100, '###0.00'),

IF(OVERALL_LEVEL_2_GROUP = 'Supply Chain',

NUM(

(

  (COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY)) /

    (COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_LATE_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_NO_FORECAST_COVER_FLAG = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY) +

  COUNT({$<MAI_ORDER_DELIVERED_ONTIME_TO_MONTH_TO_PLANNED_DATE = {1}>} DISTINCT MAI_PURCHASE_ORDER_KEY))

) * 100, '###0.00'),

NUM(

(

  (COUNT({$<FORECAST_MONTH_DELIVERED_EARLY={1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME={1}>} DISTINCT PURCHASE_ORDER_KEY)) /

    (COUNT({$<FORECAST_MONTH_DELIVERED_EARLY = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_ON_TIME = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<FORECAST_MONTH_DELIVERED_LATE = {1}>} DISTINCT PURCHASE_ORDER_KEY) +

  COUNT({$<NO_FORECAST_COVER_FLAG = {1}>} DISTINCT PURCHASE_ORDER_KEY))

) * 100, '###0.00')

))))))

Cheers again.

[marcus_sommer]

Your expression is extremely error-prone and I suggest you tried to simplfy it. One way could be to calculate some thing more within the script and another would be to integrate the if-loops within the set analysis, the use of rangesum() instead to add everything with "+" and the using from (parametrized) variables for the expression-parts, see here an example: Re: Simplify the code if possible.

- Marcus

[#7]

Hi Marcus,

I agree it is complex but I have inherited it and can't risk breaking it for what I need it for.

I have actually solved the issue that I had.  On top of the solutions that sinanozdemir provided there was also an issue with following part:

(COUNT({$<DISTINCT PURCHASE_ORDER_KEY) + 

  COUNT({$<DISTINCT MAI_PURCHASE_ORDER_KEY))

Regards

Graham

[#8]

Morning,

I have solved my issue with a combnation of suggestions and other observations.

Many Thanks

Graham