Qlik Community

Qlik Sense Documents & Videos

Documents & videos about Qlik Sense.

Creating Mini Chart in Qlik Sense tables

Partner
Partner

Creating Mini Chart in Qlik Sense tables

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:

Capture.PNG

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:

Capture.PNG

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:


Capture.PNG


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:

Capture.PNG



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:

Capture.PNG

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,

Comments
MVP & Luminary
MVP & Luminary

Neat solution. I like finding ways to solve problems without extensions, for situations where extensions are not an option.

Where extensions are possible, the Table extension from VizLib does mini-charts and a lot more besides.

https://www.vizlib.com/table/

0 Likes
Contributor II
Contributor II

thank you for your detailed how to.


In addition, to make it easier to read declare a variable for:

car_sales = (sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))

Then it's just a question of creating a custom measuer with your code

=repeat('█', rangemax(rangemin( ceil(((column(1)/column(2))-1)*2.5),10),0)

                  )


Also,

If you want to avoid having your columns being presented, with some changes you can apply your formulas directly

repeat('█',

                  rangemax(rangemin( ceil((($(car_sales ))-1)*2.5),10),0)

                  )   & num($(car_sales)),'# ##0,00%')

                 


Cheers


0 Likes
Contributor II
Contributor II

Just a correction on the your code

& num((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))),'# ##0,00%') will cause a visual bug


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

0 Likes
Creator
Creator

Hi if you haven't already found the solution  for your requirement  hope this helps.- I had a similar requirement and was able to get this far.

Pivot table :

This is my measure expression

repeat('█', round(aggr(num(sum(numbers),'0'),Rating,Student)/noofcolumns(total))) & '-' & aggr(num(sum(numbers),'0'),Rating,Student)

used a BG color as well :=rgb(245,245,245)(optional)

and text color expression is :

ColorMix1( (rank(total column(1))/(noofrows(TOTAL)/2))-1 ,rgb(15,97,19), rgb(36,242,19))

                 

bar1.png

thanks

Creator II
Creator II

Hi,

I have tried to implement your minichart string...

 

=repeat('█',

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

                  )

 

in my application  this is not working anymore?!

0 Likes
Creator II
Creator II

Can't upload the sample here, therefore uploaded it in a new thread:

https://community.qlik.com/t5/New-to-Qlik-Sense/MiniChart-in-Table/td-p/1519647

0 Likes
Contributor
Contributor

Hi ,

I have  coloumn of perTEU , i tried to apply the above expression but its not working .... can anybody help me ??? 

 

 

 

Regards 

Sunil

0 Likes
Creator III
Creator III

Hi Omar,

Thank you very much for sharing this code

0 Likes
Contributor
Contributor

activity gauge.JPGprogress bar.JPGHi everybody How can I insert an activity gauge in Qliksense  with pivot table? like QlikView and how to set three colors for 0 -80%   is green , to 80%-100% color yellow and more than 100% is red?

Or maybe  like this post , we have a progress bar but I need that every bar has three color if the values is more than 100% (progress bar.JPG)

Include two files (activity gauge.JPG) and (progress bar.JPG)

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2017-03-28 05:27 AM
Updated by: