Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More

Creating Mini Chart in Qlik Sense tables

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

Creating Mini Chart in Qlik Sense tables

Last Update:

Mar 28, 2017 5:27:27 AM

Updated By:

OmarBenSalem

Created date:

Mar 28, 2017 5:27:27 AM

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
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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/

rafaeljmvicente
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
rafaeljmvicente
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
prananellutla
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

chriys1337
Creator III
Creator III

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?!

chriys1337
Creator III
Creator III

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

Sunil
Contributor II
Contributor II

Hi ,

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

 

 

 

Regards 

Sunil

amayuresh
Creator III
Creator III

Hi Omar,

Thank you very much for sharing this code

OsvaldoLara
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
PWJ67
Contributor II
Contributor II

Very frustrating that Cell Bars didn't exists in Qlik Sense.

I've tried 2 thinks, but it's not what I need :

PWJ67_0-1666369869730.png

Here is the code for the left column in mauve and grey (background code) :

if(Column(2)>0,
rgb(
(35+(noofrows(TOTAL)+rank(TOTAL column(2))))+(12*(rank(TOTAL column(2))-1)),
(35+(noofrows(TOTAL)+rank(TOTAL column(2))))+(12*(rank(TOTAL column(2))-1)),
(82+(noofrows(TOTAL)+rank(TOTAL column(2))))+(12*(rank(TOTAL column(2))-1))),
rgb(
(330+(noofrows(TOTAL)+rank(TOTAL column(2))))-(12*(rank(TOTAL column(2))-1)),
(330+(noofrows(TOTAL)+rank(TOTAL column(2))))-(12*(rank(TOTAL column(2))-1)),
(330+(noofrows(TOTAL)+rank(TOTAL column(2))))-(12*(rank(TOTAL column(2))-1)))
)

 

And the one for the little cells to put in measure code :

repeat('■',rangemax(rangemin( ceil(((fabs(column(2))*20))),10),0))&Column(2)

 

 

 

 

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