QlikView Documents

[Kushal_Chawda]

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

see also,

Control Chart

Benford's law

Bollinger Bands

Rolling N months

Regards,

Kushal

[kkkumar82]

Hi Kush,

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

[satishkurra]

Excellent trick...

[#4]

Excellent

[Chanty4u]

Nice Kush..

[qlikviewwizard]

Good post dude.

[swuehl]

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:

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)))

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.

[kristof_j]

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.

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]

Hi,

Very helpful post 🙂

[dapostolopoylos]

Excellent tip, thank you very much!