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!

[#2]

does anyone have any idea?

[kaushiknsolanki]

Hi,

   Make sure that you have checked this too.

   Properties -> Presentation Tab -> Suppress Zero-Values and Suppress MIssing.

Regards,

Kaushik Solanki

[#4]

hi Kaushik,

i want zeroes to appear on the chart so i cannot use suppress zero-values...

i want to remove invalid records that do not even have a bar on the chart, zeroes still have bars on the chart but of cuz it's stated to be 0%...

[kaushiknsolanki]

HI,

   Can you please upload the qvw file and the desired o/p.

Regards,

Kaushik Solanki

[#6]

hi,

i'm unable to upload/attach anything now due to some restrictions at my workplace, will post a screenshot of the problem when i get home...

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

It will depend on your expression. For example, Sum(Amount) when Amount equals to null is equal to zero, so the result is actually zero. So you need to filter those values from your aggregation, for example using set analysis

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

P() will return all possible values of USE, whether it is or not greater than zero, but will not return null values. So this expression in your chart, with the settings you already have as suggested by Kaushik should return what you expect.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#8]

check out the 1st and 2nd bar, those are the 0% ones which i wanna retain...

however, look at the 3rd and 4th "bar", they are invalid since they are present in only 1 table and not the other 2 tables. i dont know why those few records are like that, probably due to some poor management of the database previously. rightfully, records appearing in 1 table will appear in the other 2 tables.

thus, i cannot suppress 0 values since i wanna retain the 1st and 2nd bar... supressing null values does not remove the 3rd and 4th "bar" though...

[#9]

hello guys, any idea?

[kaushiknsolanki]

Hi,

   I would say without the proper sample data, its not possible to understand your problem and find the solution.

   As you know what your data look like, so can you please generate the sample data same like the one you have and post here, so that we can try to solve your problem.

Regards,

Kaushik Solanki