Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Show negative values on Charts

cancel
Showing results for 
Search instead for 
Did you mean: 
Kushal_Chawda

Show negative values on Charts

Last Update:

Sep 21, 2022 1:04:33 PM

Updated By:

Sue_Macaluso

Created date:

Apr 2, 2016 6:33:07 AM

Attachments

Hi,

I have seen many posts on community for showing negative values on charts like Block chart, pie chart etc. There is a solution available on community, but I thought to create the document so that everyone can utilize the same.

Lets say I have below data set

LOAD * INLINE [
Country, Score
US, 4971.765
UK, 4782.54
Japan, -200.941
India, 1412.181
Russia, 2445.292
]
;

Now I want to create the Block chart with above data set. So create the block chart with dimension as country and expression as sum(Score) , as soon as you create the chart you will get the error message that "positive and negative values in chart". But you can still display the negative values on chart with slight modification in expression.

Steps to create the block chart with negative value

1) Create the block chart

   Dimension : Country

   Expression : dual(sum(Score),fabs(sum(Score)))


If you want to display the distribution in % then use below expression


dual(num(sum(Score)/sum(TOTAL Score),'#0.00%'),fabs(sum(Score)))

Using dual function you can display the negative value with the base as positive value.

2) Go to chart properties -> Number Tab and check the Number format setting always as "Expression default". If the number format setting is not the expression default then negative values will be displayed as positive and also data will be displayed in wrong format. So if you want to provide any format like %, money you can define in the Dual expression itself using num function

Please see the attached application. In this application I have used the Fast change option so that you can see the results for multiple chart type as well

Untitled.jpg

see also,

Control Chart

Benford's law

Bollinger Bands

Rolling N months

Regards,

Kushal

Tags (1)
Comments
kkkumar82
Specialist III
Specialist III

Hi Kush,

Its a good trick , Expecting more like this from you, especially for beginner community.

satishkurra
Specialist II
Specialist II

Excellent trick...

Not applicable

Excellent

Chanty4u
MVP
MVP

Nice Kush..

qlikviewwizard
Master II
Master II

Good post dude.

swuehl
MVP
MVP

Using dual() function to create a text representation that shows something different from the numeric value is indeed a useful technique and very helpful in several scenarios.

I am not sure if modifying a block or pie chart in this way is really useful, though. IMO, a pie chart or block chart shows parts of a total, and if the total is built from positive and negative values, there is a reason why Qlik refuses to create these kind of charts by default: It's hard for the user to understand the chart.

For example, just slightly changing your sample numbers results in this chart:

2016-04-03 14_06_43-QlikView x64 - [C__Users_Stefan_Downloads_Negative values.qvw_].png

Users expect to get half of the pie when the percentage is 50%, don't you think so? And what does -25% indicate in such a representation?

You would get a correct absolute percentage with regard to slice are when using

=dual(num(sum(Score)/sum(TOTAL fabs(Score)),'#0.00%'),fabs(sum(Score)))

2016-04-03 14_07_32-QlikView x64 - [C__Users_Stefan_Downloads_Negative values.qvw_].png

But still, it's hard to understand what this image should represent.

Maybe a bar chart would show the negative and positive values better.

Well, all above just my 0.02€, there might be scenarios where you want to use this approach even when using charts where it doesn't make sense in general, IMHO, I just don't see any.

If you do, I would be happy to see a use case.

Regards,

Stefan

Kushal_Chawda

You are correct swuehl‌. But there could be a scenario, where total need to be specific to considering the negative values as well to calculate the %. So in this case sum(TOTAL fabs(Score)) will not be helpful. Though here intention was just to show how we can display negative values on charts. Based on business need we can modify the expressions in dual function as we have flexibility to write the expression.

0 Likes
kristof_j
Creator III
Creator III

I also believe that a bar chart is easier to interpret and less confusing as swuehl‌ suggests.

You can make it seem as a part of a whole by letting the bars stick together.

Capture.JPG

But my point being is that moest of the time you can solve problems by changing your visualisation.

And I believe in the cliché 'less is more'. The less different expressions, the  less overhead the more you and your colleagues understand the app.

Though I can see some use cases in a table chart where you want to sort values by largest deviation and then you only need the fabs() function and not the dual().

But if someone has a use case I would also love to see it.

Anonymous
Not applicable

Hi,

Very helpful post 🙂

0 Likes
dapostolopoylos
Creator III
Creator III

Excellent tip, thank you very much!

0 Likes
Version history
Last update:
‎2022-09-21 01:04 PM
Updated by: