24 Replies Latest reply: Aug 18, 2017 3:16 PM by Russell Glenn

# How to show bottom 10 business units and excluding zero values in Bar chart

Hi Guys,

I am struggling with displaying the bottom 10 business units and excluding the zero values (the data that was not input yet). When I check do not show zero values then I do a limitation on a fixed number of bottom 10, I get no data to display.

I think this is because it is trying to pull the zero data units in and there are none. How do I exclude the business units that have 0 in for revenue and display only the bottom 10 that actually have a number in there?

Thanks,

Russ

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

Would you be able to share a sample?

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

A sample of what exactly?

When I select bottom 10, I see the bottom 10 that are all zero \$. I need to exclude those and get to the bottom 10 that actually have data.

If I select exclude zeros, nothing shows up at all.

Thanks!

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

May be use set analysis to exclude 0

{<Revenue -= {0}>}

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

Thanks for your reply...Where would I place this set analysis? In the overall expression or in the limitation expression? Thanks Sunny

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

I would overall expression

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

I am also getting a garbage expression after -= part of that formula...any thoughts?

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

What are your chart dimensions and your expression?

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

Chart dimensions are Product Line

and the expression is:

sum(if(Year>2016 and [Financial View]='Budgeted',[Revenue (dollars)],if(Year<2017 and [Financial View]='Actual',[Revenue (dollars)],0))/1000000)

This is current equation for revenue at the present time

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

Try this expression

Sum({<[Product Line] = {"=Sum(If(Year > 2016 and [Financial View] = 'Budgeted', [Revenue (dollars)], If(Year < 2017 and [Financial View] = 'Actual', [Revenue (dollars)], 0))/1000000) > 0"}>} If(Year > 2016 and [Financial View] = 'Budgeted', [Revenue (dollars)], If(Year < 2017 and [Financial View] = 'Actual', [Revenue (dollars)], 0))/1000000)

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

I get the OK status but no go on showing the bar chart - here we just get 0 values then when I select do not show zero values, we get cannot display

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

It might be easier if you are able to share a sample

Preparing examples for Upload - Reduction and Data Scrambling

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

Not sure if we can do this...do you have any other thoughts on the formula? -> even doing it with a simple [Revenue (dollars)] field?

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

Sunny - Going to repost this as the original solution did not work when filtering that the other community member suggested

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

I am also getting a garbage expression after -= part of that formula...any thoughts?

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

Could you explain how to type this into the expression? Having trouble with it

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

Hi,

May be as at attached file?

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

I do not have a desktop version of qlik sense - do you have any thoughts of what I can do in app to fix?

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

Expression got "OK" status but still shows zero values in bar chart

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

In the dimension, you must clear the "Show zero values"

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

Then I get no data to display, which I don't understand

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

So, this worked just needed a "-" before the "=" sign

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

This does not work when we filter by filter pane on certain selections....

Any thoughts?

• ###### Re: How to show bottom 10 business units and excluding zero values in Bar chart

I am trying to place my revenue expression within the Revenue bold item here but still having problems with the expression. Revenue is a field. Does anyone have ideas on how to exclude zero values?

Thanks

{<Revenue -= {0}>}