Omit the value of zero in a table chart

[pandreozzi]

I added a table chart to my sheet and the primary key have values of zero ,0, that I want to omit from the table box. I see articled regarding charts and dimensions but nothing stands out for table boxed. Is there a way to exclude the value zero and where do I apply this. I found this expression regarding the chart. The P_Key is my field in the table that I need to key off of.

=if(sum(total P_KEY)>0,Sum(P_KEY)

Thanks, Paul

[antoniotiman]

Hi Paul,

You can't add expression in Table Box.

Maybe You need  Straight Table (or List Box) with expression

If(P_KEY > 0,P_Key)

Regards,

Antonio

[manas_bn]

I am assuming P_Key is a dimension on your chart and that has values = 0?

If so, you can add a Calculated Dimension and use this expression - "=if(P_KEY<>0,P_KEY,null())". Then Check "suppress when value is null".

OR

Add this as an expression: "=Sum({<P_KEY= {">0"}>}Sales)"

[pandreozzi]

Thanks Manas. the first expression worked fine. I have seen that but was not sure where to add it. Appreciate your quick response. One other question if I have a table box how would this apply?

[sergio0592]

For table box, can't see a solution except excluding PK=0 during the load statement. Use Where PK>0 in load script.

[pandreozzi]

Thanks, I ended up changing my table box to a chart and use Manas's suggestion and that worked fine. Thanks everyone for the quick response.

[manas_bn]

I guess you could use the below in the load script. And in the Presentation tab in the table box, you can omit null values.

This will ensure you still have rows with P_KEY as 0, but won't be available in the front end table box.

Load

if(P_KEY<>0,P_KEY,null()) as P_KEY,

Sales

from Sales.qvd;

[pandreozzi]

Ok. let me try this and let you know if it worked as expected.

Thanks again

[pandreozzi]

This also worked. I just needed to modify it a bit to fit into my load. I was already loading the SWSITEID as P_KEY and just needed to change that. Once done and the option to omit null values in the presentation tab of the table box this worked.

if(SWSITEID<>0,SWSITEID,null())AS P_KEY, //primary key

[qv_testing]

you can try this in expression

RangeMax(yourExpression, 0.1)

Now value starts with 0.1