Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
AdrianoEllero
Contributor III
Contributor III

How to aggregate items within the same measure in a bar chart

I want to show per month a stacked bar chart collecting as measure the turnover of several geographical areas.
There are so many geographical areas that it would be convenient to aggregate some areas in groups, like North, East, South, and West.

How to get the stacked bar chart with the area groups instead of the singular area?

Any hint?

AdrianoEllero_0-1736879606555.png

chart with all areas ungrouped 

 

Labels (3)
3 Solutions

Accepted Solutions
JandreKillianRIC
Partner Ambassador
Partner Ambassador

Hi @AdrianoEllero 

I see you don't have access to the script. You can do the grouping in a calculated dimension. 

Below is a image showing the normal bar chart, Group Bar as well as the raw data. 

JandreKillianRIC_0-1736920870827.png

Change your dimension to something like this. 

=Pick(
          Match(ActualValue, 'Val1', 'Val2', 'Val3', 'Val4', 'Val5'),
               'Category 1', 'Category 1', 'Category 2', 'Category 2', 'Category 3')

 

Please let me know if this works. 

 

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn

View solution in original post

MatheusC
Specialist II
Specialist II

The script solution is where I would go.
As now said it is not possible to access the editor, so the solution presented by @JandreKillianRIC  should help on the graphical side.

- Health

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!

View solution in original post

AdrianoEllero
Contributor III
Contributor III
Author

Thank you so much @JandreKillianRIC 

It works easily and perfectly

Now I would like to add a second step: some values are defined 'Val1', 'Val2', some other have to be collected all value which ends with "M" for a group M, or "R" for another one.

So I image something like this:

=Pick(
          Match(ActualValue, 'Val1', 'Val2', '', *'M', *'P'),
               'Category 1', 'Category 1', 'Category M', 'Category P')

how to collect *****M or *****P values?

Maybe using wildmatch function?


 

 

View solution in original post

7 Replies
MatheusC
Specialist II
Specialist II

Hi, @AdrianoEllero 

Create a new field in your script grouping the areas into 4 categories. I would say something like this:

// creating an inline mapping table
MapArea:
Mapping
Load * Inline [
Areas,Group
Area1,North
Area2,North
Area3,North
Area4,Sul
Area5,Sul
Area6,East
Area7,East
Area8,West
Area9,West
Area10,West
];

//*******************************************************//

// Applymap function returns the output inserted according to the mapping table above

YourTable:
Load
*,
ApplyMap('MapArea',Areas) as AreasGroup
from[](qvd);


Note: Modify to your real fields


Regards, Matheus

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
Zenababy
Contributor II
Contributor II

Hello, my dear, how are you doing today, I am Zena 24 years old girl, I saw your profile today
I like It, I decided to contact you because i have
an important issue i need to disclosed to you
privately, please try to contact me with my
email address here ( zenahammed@hotmail.com )
if i hear from you i will tell you more about
me with my photos, thanks hoping to hear from
you, yours Zina.
AdrianoEllero
Contributor III
Contributor III
Author

I guess I have no rights to create a new field...

AdrianoEllero_0-1736885124981.png

this is all I have on the left side of the screen

JandreKillianRIC
Partner Ambassador
Partner Ambassador

Hi @AdrianoEllero 

I see you don't have access to the script. You can do the grouping in a calculated dimension. 

Below is a image showing the normal bar chart, Group Bar as well as the raw data. 

JandreKillianRIC_0-1736920870827.png

Change your dimension to something like this. 

=Pick(
          Match(ActualValue, 'Val1', 'Val2', 'Val3', 'Val4', 'Val5'),
               'Category 1', 'Category 1', 'Category 2', 'Category 2', 'Category 3')

 

Please let me know if this works. 

 

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn

MatheusC
Specialist II
Specialist II

The script solution is where I would go.
As now said it is not possible to access the editor, so the solution presented by @JandreKillianRIC  should help on the graphical side.

- Health

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
JandreKillianRIC
Partner Ambassador
Partner Ambassador

Hi @AdrianoEllero 

Did this solution work for you? 

Regards - Jandre

* Please remember to mark the solution that solved your question

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn

AdrianoEllero
Contributor III
Contributor III
Author

Thank you so much @JandreKillianRIC 

It works easily and perfectly

Now I would like to add a second step: some values are defined 'Val1', 'Val2', some other have to be collected all value which ends with "M" for a group M, or "R" for another one.

So I image something like this:

=Pick(
          Match(ActualValue, 'Val1', 'Val2', '', *'M', *'P'),
               'Category 1', 'Category 1', 'Category M', 'Category P')

how to collect *****M or *****P values?

Maybe using wildmatch function?