Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How do I show a blank when there is negative or zero value?

Hi All,

I wish to display a pie chart.

However, there are some negative values and the app will show the error "The chart is not displayed because it contains negative or zero values".

I understand that a pie chart is never good to display a negative value. But is it possible for the app to show a blank space instead of displaying the error?

thanks in advance!

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

I just found out that I can use "Calculation Expression" under data handling to mute the error message!

For example:

if(sales>0,sales,0)

View solution in original post

11 Replies
quriouss
Creator III
Creator III

Could you do something in your formula like;

    IF ( SUM(SALES)<0 , null() , SUM(SALES) )

(I haven't tried this so i make no guarantee...)

rubenmarin

Hi Guohao, I don't understandand where you want to show 'blank': As a portion of the pie chart? Don't show any message and all the chart goes blank?

The first one isn't possible as if there are negative values, the positive will be more than 100% of the total, and that's what can't be drawed.

For the second you can use the condicional show of the design tab, using an expression like:

If(Count(Aggr(Only({<Customer={"=Sum(Sales)<0"}>} Customer), Customer)), 0, 1) //If there is at least a negative sales for a customer, don't show the chart.

One option to avoid negative is using a calculated dimension to only show dimensions with positive values, ie:

Aggr(Only({<Customer={"=Sum(Sales)>0"}>} Customer), Customer)

In presentation tab you can add chart text to advise to user:

If(Count(Aggr(Only({<Customer={"=Sum(Sales)<0"}>} Customer), Customer)), 'There are negative values wich have been excluded')

rubenmarin

BTW, pie chart looks nice but if negative values are important consider to change to bar chart.

Anonymous
Not applicable
Author

Hi All,

Thanks for all your replies!

Quriouss‌: Your formula is fine, but the app is still showing the error: The chart is not displayed because it contains only negative or zero values due to the null values i suppose.

RubenMarin‌ Bar charts definitely helps in showing the negative but it doesn't show the distribution well in terms of proportions.

I guess what I am asking is if we can just get the pie chart to show nothing (i.e. blank space) instead of the error message.

What I have in mind is to set a blank error handling message instead of the "The chart is not displayed because it contains only negative or zero values".

Is it possible?

ogster1974
Partner - Master II
Partner - Master II

Have you thought of using a Heat map to display proportionality?  Pie charts are great when your slices add up to 100% but can be misleading when negatives are involved.

Regards

Andy

Anonymous
Not applicable
Author

Hi Andy,

Thanks for your reply!

I tried using the Treemap in Qilk Sense and it has the same error when there are negative values.

The data i am displaying represents profit so the target audience is more interested in the profit. The loss has been summarized in another chart and a pivot table.

Using absolute is out of the equation, hence, I am looking to either show only the profit, or completely not show the pie chart at all. I chose the latter but it is showing me the negative value error.

Is there a way for the app to not display that error?

ogster1974
Partner - Master II
Partner - Master II

As you've worked out its not an error rather Qlik handling of inappropriate values for the type of chart you are using.

My recommendation would be to use another chart appropriate for showing losses as well as profit for the complete picture or in the measure use an IF statement to exclude the negative values either by nulling the value then excluding dimensions with null value or setting it to zero as others have posted above.

Regards

Andy

Anonymous
Not applicable
Author

I just found out that I can use "Calculation Expression" under data handling to mute the error message!

For example:

if(sales>0,sales,0)

Not applicable
Author

Hi,

I tried it but it didn't worked out for me.

my code:

If(Sum(amount_aw)>0,Sum(amount_aw),0)

Also worth noticing is that i give Qlik Sense only positiv data because i cut up the negativ values in sql statement.

hope u can help me