topic Re: Nested IFs in QlikView

Nested IFs

Thu, 18 Jun 2015 14:00:06 GMT

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

Re: Nested IFs

sinanozdemir — Thu, 18 Jun 2015 15:34:19 GMT

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

Re: Nested IFs

Thu, 18 Jun 2015 15:52:59 GMT

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',