Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
Thanks u so much , it is working now .
I Salute u Sir
Thanks,
AS
Its my pleasure to help you people.
Regards,
Jagan.
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