suppress null but not 0

[curiousfellow]

I have a table with year,sales,profit

I want a pivot containing year and profit in percentage of sales.

For some years, profit is not available yet (isnull)

Some years have a profit of 0

In the pivot table i want to show the years that have 0 profit but not the years where profit is available.

When I choose to suppress zero values the years with 0 are missing

When I don't suppress zero values, years with null value are displayed too.

How to solve this ?

I enclose an example

I hope you will understand that the figures are not real , but just a simplified example of the problem i am struggling with

[arulsettu]

you want to show 0 instaed of '-' this?

[Mark_Little]

Hi

Try IF(LEN(Profit) > 0, Profit)

Mark

[rubenmarin]

Hi, in your sample 2006 and 2007 aren't null, they have the string 'null()' stored. If you really want to test the null values you can try with:

test:

load year,total,If(Len(profit)>0, profit, null()) as profit inline

[year,total,profit

2000,20000,100

2001,20010,200

2002,20200,300

2003,20300,0

2004,20400,0

2005,20500,0

2006,20600,

2007,20700,

]; // The empty space is not a Null() either, you have to check it and tranform in a real Null()

Or you can keep the 'null()' string and check this value to load it as a real Null() value:

If(profit='null()', null(), profit)

With these nulls it works as you expected, maybe you'll need to check the 'supress when value is null' in the year dimension.

[sunny_talwar]

This?

Expression:

= If(If(not IsNull(profit), profit/total) = 0, Dual('0.00%', 0.00001), Num(If(not IsNull(profit), profit/total), '#,##%'))