Sicript help ????

[amit_saini]

Hi Folks,

I'm using Pivot table  and I'm looking for solution below:

if Average Demand  = „0“ and Actual Inventory Available = 0  and Actual Inventory Value= “0” then don’t show the Material number  in the report.

I'm trying something  like below , but not working

=IF( not isnull(ACTUAL_INV_VAL) and  not isnull(ACTUAL_INV_QTY)  and not isnull(AVG_DEMAND), Material_MATNR, NULL())

Thanks,

AS

[jagan]

Hi Amit,

It is 0 not 3, typo mistake.

If(Column(1) >0 AND Column(2) > 0 AND Column(3) > 0,

CURRENCY, Null())

Regards,

Jagan.

[amit_saini]

Hi Jagan,

Still its not working the way I want , please see below:

=if(AVG_DEMAND>0 and ACTUAL_INV_VAL >0 and ACTUAL_INV_QTY>0,Material_MATNR,Null())

If I'm using this as dimension for sure showing me material number if I'm having any value against AVG_Demand  , ACTUAL_INV_VAL and ACTUAL_INV_QTY but  if any one of them is having value and others are 0 than also Material number should appear.

Any Help here?

Thanks,

AS

[jagan · Accepted solution]

Hi,

Don't use this in dimension, try the way I provided in my earlier post, if all the rows returns 0/null then the Material number automatically removed when you supress 0 and Null values in the Presentation tab.

Regards,

Jagan.

[amit_saini]

Thanks u so much , it is working now .

I Salute u Sir

Thanks,
AS

[jagan]

Its my pleasure to help you people.

Regards,

Jagan.

[amit_saini]

Hi Jagan,

Just to share with you , I got one more logic and it is working fine if I'm calling material number as dimmension:

LOAD

     COMP_CODE,

     PLANT,

     PROFIT_CTR as Profit_Center_key,

     PROD_GRP,

     //right([MATNR],10) as [Material_MATNR],

     IF( not isnull(ACTUAL_INV_VAL) and  not isnull(ACTUAL_INV_QTY)  and not isnull(AVG_DEMAND), right([MATNR],10), NULL()) as [Material_MATNR],

     MAKTX,

     DATE_HORIZON,

     If(AVG_DEMAND=''or AVG_DEMAND='-',0,AVG_DEMAND) as AVG_DEMAND ,

     If(ACTUAL_INV_VAL=''or ACTUAL_INV_VAL='-',0,ACTUAL_INV_VAL) as ACTUAL_INV_VAL,

     If(ACTUAL_INV_QTY=''or ACTUAL_INV_QTY='-',0,ACTUAL_INV_QTY) as ACTUAL_INV_QTY,

     CUST_DEMAND,

     HORIZON,

     VALUE_PER_PC,

     //ACTUAL_INV_VAL,

     CURRENCY,

     //ACTUAL_INV_QTY,

     UNIT,

     ACT_INV_QTY_AVL,

     ACTUAL_DOH,

     TARGET_DOH,

     TARGET_INV_QTY,

     TARGET_INV_VAL,

     DATE_MODIFIED as Datefield,

     Date(DATE_MODIFIED,'YYYYMM') as Date_Key

FROM

(qvd)

where   (If(AVG_DEMAND=''or AVG_DEMAND='-',0,AVG_DEMAND)+ If(ACTUAL_INV_VAL=''or ACTUAL_INV_VAL='-',0,ACTUAL_INV_VAL) + If(ACTUAL_INV_QTY=''or ACTUAL_INV_QTY='-',0,ACTUAL_INV_QTY)) <>0 ;

Thanks,

AS