article Creating Mini Chart in Qlik Sense tables in Qlik Sense Documents

Creating Mini Chart in Qlik Sense tables

OmarBenSalem — Tue, 28 Mar 2017 09:27:27 GMT

Hi all,

Mini Charts have always been a good feature in QlikView and It's really 'frustrating' that this has not been added (yet) in Qlik Sense.

With hopes that the Qlik Team will think about such improvements for the next releases, let's discuss here how to create our own mini chart in a Qlik Sense Table.

The following expressions might seem 'complicated' at first sight, but remember that It will be generic.

You'll only have to change your : Dimension(s) and Measure to adapt to your need.

Thus, their use will be very easy.

Let's begin :

Suppose we have:

As a dimension: OICA Region

As measures:

Sum(Sales)

Sum("Commercial Vehicle Sales")

Sum(Sales) / (Sum(Sales)+Sum("Commercial Vehicle Sales"))

Here is our current table:

Now, we want to add, a mini 'bar' to represent the %:

How to do so:

As an expression:

=repeat('█',

rangemax(rangemin( ceil(((column(1)/column(2))-1)*2.5),10),0)

)

With that, we repeat the square sign and precise the limits.

And as a text Color expression:

if( (sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))>=0.8,Green(),

if((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))>=0.7,Yellow(),

Red()))

Result:

We can also Add the % in front of the bar by adding it in the expression:

From :

=repeat('█',

rangemax(rangemin( ceil(((column(1)/column(2))-1)*2.5),10),0)

)

To:

=repeat('█',

rangemax(rangemin( ceil(((column(1)/column(2))-1)*2.5),10),0)

) & num((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))),'# ##0,00%')

Result:

You can even alter it as follow:

repeat(chr(09608),round( sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])) * 10))

&repeat(chr(09617),10-round( sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])) *10))

&num((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))),'# ##0,00%')

&if(sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))>0.7,'▲','▼')

result:

Now, let's suppose we want to add another mini chart, a trend one, to see the % expression by Year:

If it has increased or decreased from a year to the next one.

How to do so?

Expression:

=concat(

aggr(

if((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))) > above((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))),

'▀',if((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))) < above((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))),'▄',' ')

)

,[OICA region], (Year,(NUMERIC, ASCENDING))

)

,'',[OICA region])

Wirh:

Red: Our Dimension

Green: The trending dimension

Blue: our expression compared to the previous one (by year, the trending expression)

If expression by Year > expression Previous Year then , Square Up

If expression by Year < expression Previous Year then , Square down

Result:

Special thanks to blog Von Heldendaten in which I found this:

QlikView + Qlik Sense Blog von Heldendaten: Qlik Sense Calendar Measures & "Minicharts" in Tabellen.

Hope you like it !

Omar,

Re: Creating Mini Chart in Qlik Sense tables

brunobertels — Tue, 04 Apr 2017 08:14:03 GMT

Hi Omar

Thanks à lot for this very interesting post

Re: Creating Mini Chart in Qlik Sense tables

Anonymous — Fri, 21 Apr 2017 15:03:11 GMT

Cunning yet simple. I like it.

Re: Creating Mini Chart in Qlik Sense tables

rodjager — Mon, 24 Apr 2017 04:26:02 GMT

Simple and very effective - thanks for sharing this Omar.

Re: Creating Mini Chart in Qlik Sense tables

ahaahaaha — Thu, 27 Apr 2017 12:08:37 GMT

Hi Omar,

Thanks for the very useful information.

Re: Creating Mini Chart in Qlik Sense tables

stantrolav — Thu, 27 Apr 2017 12:32:35 GMT

Just copy and paste them: ▁▂▃▄▅▆▇█

{"▁▂▃▄▅▆▇█▇▆▅▄▃▂▁","▂▁▄▃▆▅█▇","█▇▆▅▄▃▂▁▂▃▄▅▆▇█","▁▅█▆▅▃▂▇▄▅"}

Use ORD('▇') to get CHR code.

Cool post. Thanks for idea.

Re: Creating Mini Chart in Qlik Sense tables

Anonymous — Fri, 26 May 2017 11:02:43 GMT

Thank you so much.... It was very helpful...

Re: Creating Mini Chart in Qlik Sense tables

Anil_Babu_Samineni — Mon, 29 May 2017 04:45:45 GMT

Nice to have, can i expect any thing related same in Trend like Line chart ?

Re: Creating Mini Chart in Qlik Sense tables

JaMajka1 — Wed, 31 May 2017 13:32:43 GMT

Great idea! Thank a lot

Re: Creating Mini Chart in Qlik Sense tables

Anil_Babu_Samineni — Wed, 31 May 2017 16:27:36 GMT

I got it, By help of extension Qlik Branch

Re: Creating Mini Chart in Qlik Sense tables

magdalenatudose — Tue, 06 Jun 2017 08:01:31 GMT

Hi,

Can someone give me a simple example of using mini chart in qlik sense?

I don't succeed apply the example from this page. My result is the same graphic image on all the column of table.

Thank you,

Magdalena

Re: Creating Mini Chart in Qlik Sense tables

JaMajka1 — Wed, 07 Jun 2017 19:45:22 GMT

Try the simplest example I could imagine (it works on my desktop):

[data]:

Load

RowNo() as ID

AUTOGENERATE 3;

and then in the table with dimension ID use the expression repeat('█',ID).

Re: Creating Mini Chart in Qlik Sense tables

magdalenatudose — Thu, 08 Jun 2017 05:56:13 GMT

Hi Maria,

Thank you for your answer...

I am new in Qlik Sense and I don't really understand what you mean.

Can you atash me the example?

Thank you,

Magdalena

Re: Creating Mini Chart in Qlik Sense tables

prananellutla — Fri, 09 Jun 2017 17:57:40 GMT

Hi Omar, can i change the orientation of the bars to be vertical?

Re: Creating Mini Chart in Qlik Sense tables

dogvilegr13 — Mon, 28 Aug 2017 08:30:46 GMT

It works like a charm.

The only issue is that code

repeat(chr(09608),round( sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])) * 10))

&repeat(chr(09617),10-round( sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])) *10))

&num((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))),'# ##0,00%')

&if(sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))>0.7,'▲','▼')

The two bars are not overlapping but they are in sequence.

Thanks again for the code/example!

Re: Creating Mini Chart in Qlik Sense tables

tauceef9 — Mon, 16 Oct 2017 11:13:11 GMT

Hi Omar,

Thanks for the post, It helped me a lot.

I have one requirement on the same:

Can we have multiple color in the single row for different bars? I have combined two bars in a row using

repeat('▀',round( sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])) * 10))

& repeat('▀',10-round( sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])) *10))

now what I need is to color both bars with different colors, is this possible to do?

Looking forward for your response.

Regards,

Tauceef

Re: Creating Mini Chart in Qlik Sense tables

sabahat_quazi — Wed, 22 Nov 2017 07:11:11 GMT

Hi All,

Is there any way to show the negative values in above mini bar chart?

Regrads,

Sabahat

Re: Creating Mini Chart in Qlik Sense tables

wizardo — Mon, 15 Jan 2018 23:26:35 GMT

Hi there,

first i wanted to say i really enjoyed your post and the creativity it provides/provoke,

I do have a question tho,

I am using the last version of sense (sept') and i'm not sure if its the version's fault but i encounter a problem with sorting when using your expression to create the whiskers chart.

I'm not sure if its the sorting of the aggr() function or not but what happens is

that the string with ASCII block is sorted so that all the '▀' come first and then comes all the '▄' , somthing like that: '▀▀▀▀▀▄▄▄▄' .

any ideas?

I'v tried changing the sort order of the field itself in the chart properties but it didn't change anything

thanks

Wizardo

Re: Creating Mini Chart in Qlik Sense tables

seanbruton — Thu, 18 Jan 2018 17:15:48 GMT

Magic Thanks

Re: Creating Mini Chart in Qlik Sense tables

Anonymous — Fri, 23 Feb 2018 17:37:12 GMT

omarbensalem‌ Thank you for the post.

Any thoughts on showing 2 bars like this :