allowing 0 to show on bar chart but not null values

[#1]

hi guys, i have a chart to show the bottom 10 usage rate of a device, thus there would be devices that have 0% usage rate...

expression is simply usageTime/totalTime. i also placed the expression in the Sort tab so as to show only the bottom 10...

under Presentation tab, i unchecked Suppress Zero-Values so as to show 0% bar(s) on the charts...

however, null/invalid also show on up the chart (they do not even have a 0% bar)...

now, some background info about my tables. i have 3 tables with the device information, usage rate etc...

usually a device will have records in all 3 tables. however, there are a few devices which have records in only 1 table, resulting in their names appearing on the chart without a bar even when they do not have a usage rate % to show...

Suppress When Value Is Null is already checked under Dimension...

what else can i do?

thanks in advance!

[Miguel_Angel_Baeyens]

Hi,

I thought you did read the reply I posted below, but it seems you don't.

Check the attached application on how the below expression works.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[kaushiknsolanki]

Hi,

   Yes Miguel's Solution is the perfect one.

Regards,

Kaushik Solanki

[#4]

hi Miguel,

i duno y but i actually missed out ur reply with the P() function, it works for me!

anyway, i'm using the free personal edition so i'm unable to open the file u've attached. is it the same as the post which you mentioned the P() function?

nevertheless, thanks again to you and Kaushi for the help...

[Miguel_Angel_Baeyens]

Hi,

This is the script in that app:

LOAD ID, 
     If(ID = 'G', null(), USE) AS USE
INLINE [
ID, USE
A, 10
B, 20
C, 5
D, 7
E, 10
F, 30
G
H, 1
J, 0
];

Note that I'm forcing one value to be null, and another to be zero.

Then I created a new barchart, using ID as dimension and

Sum({< USE = P(USE) >} USE) / SUM(TOTAL USE)

as expression. I unticked the "Suppress Zero Values" in the chart properties, Presentation tab, and click the "Zero on bars".

"J" will show in the chart with 0 as value, but not G, that has a null.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica