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

[Anonymous]

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!

[quriouss]

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]

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]

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]

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]

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 · Accepted solution]

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)

[#10]

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