15 Replies Latest reply: Dec 12, 2014 7:59 AM by Amit Saini

Sicript help ????

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

• Re: Sicript help ????

Hi,

Try if(sum of all three <>0,field,0).

• Re: Sicript help ????

Not working!

• Re: Sicript help ????

Any help ????

• Re: Sicript help ????

Hi,

You need to use this condtion in all the expressions

=IF( [Average Demand]  = 0 and [Actual Inventory Available] = 0  and [Actual Inventory Value= 0], Your Expression Goes here)

Note : If all expressions returns 0 in a row then that is automatically removed, to do this we are using above If().

Hope this helps you.

Regards,

Jagan.

• Re: Sicript help ????

Hi Jagan,

Please see the attachment , I tried few things but still looks like approach is wrong.

Kindly help if possible.

Thanks,

AS

• Re: Sicript help ????

Hi Amit,

Please find attached file for solution, I implemented only for Currency column, do the same for remaining columns and check the presentation tab for changes.

Regards,

Jagan.

• Re: Sicript help ????

Hi Jagan,

Thanks for your response ,Just one question:

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

CURRENCY, Null())

This Column(3) > 3 should be Column(3) > 0???

Thanks,

AS

• Re: Sicript help ????

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.

• Re: Sicript help ????

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

• Re: Sicript help ????

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.

• Re: Sicript help ????

Thanks u so much , it is working now .

I Salute u Sir

Thanks,
AS

• Re: Sicript help ????

Regards,

Jagan.

• Re: Sicript help ????

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:

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

[F:\QlikView\Server\SourceDocuments\qvd\SAP\KTX_100\ZKMM_QLIK_VIEW.qvd]

(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

• Re: Sicript help ????

Also check the properties of you object:

Chart Properties -> Dimensions

- Suppress When Values is NULL should be marked

- Show All Values should be unmarked.

• Re: Sicript help ????

Hi amit,

try to use conditional show, but not sure, if it would fulfill your requirement.