Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
arixooo123
Contributor II

dividing a dimension into some pieces to better show it in a bar chart

  dears!

  I have a column in a table called city, This column has more than 100 distinct values. using city column as a dimension,

  when i create a stacked bar chart, since there are many of them(cities).  I cannot see much of that in the chart and it gets so messy.

  is it possible to break a dimension with 100 possible values into 5 groups of dimensions each has 20 values?

  Thanks for your help in advance.

Tags (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: dividing a dimension into some pieces to better show it in a bar chart

I think your way would be the best one. Like that, you do not need to hardcode it in the script and if any new groups are going to be added you will add them into your database

You can always hardcoded in your script by using nested if statement or PICK() function will solve it too.

IF nested:

if(mixmatch(City, 'Paris','Barcelona'),'Group1',

     if(mixmatch(City,'Tokio'),'Group 2',

          if(mixmatch(City,'New York'),'Group 3',

                    'Group 4'))) as City_Groups

Pick function:

Pick(Wildmatch((City),'Paris','Barcelona','Tokio','New York'),

                              'Group 1', 'Group 1','Group 2','Group 3')

                                        as City Groups

View solution in original post

13 Replies

Re: dividing a dimension into some pieces to better show it in a bar chart

Certainly is possible.

First you need to define the rules as to which city goes into which group.

arixooo123
Contributor II

Re: dividing a dimension into some pieces to better show it in a bar chart

Thanks for your instant response.

would you please explain more. how can i set that rules? in dimension tab?

Luminary
Luminary

Re: dividing a dimension into some pieces to better show it in a bar chart

Hi,

Given the amount of cities I would recommend doing this in the script. I would create a new table with City and Group. you then join this table to the table where you city is stored.

Example:

GeoDim:

Load

City,

Country

FROM...

CustomeTable

inner join

Load * inline [

City, Group

Chicago, Group 1

NY, Group 1

Miami, Group 2

Orlando, Group 2

...

];

you can then use your Group field in your chart.

JV

Partner
Partner

Re: dividing a dimension into some pieces to better show it in a bar chart

In script using pick function divide these cities into 5 groups, give it name as Cities_Grouped.

In your chart: Dimensions->Edit Groups->New->Drill Down and select Cities_Grouped and Cities.

When you go to your chart you should be first presented with Cities_Grouped, clicking on any of the Cities_Grouped will then drill down to your 20 Cities in this group.

arixooo123
Contributor II

Re: dividing a dimension into some pieces to better show it in a bar chart

Dear Friend, would you please do it on a sample? i am not actually good at scripting.

I uploaded the sample.

I'd like to see what percentage of each city population lives in different temperature conditions.

the table has just 3 columns.

dimension: City, Temp

Expression

Sum(Population)/Sum(Total <City>Population)

Type of chart: Stacked Bar Chart

saumyashah90
Valued Contributor

Re: dividing a dimension into some pieces to better show it in a bar chart

Hi Arixoo,

Check this file,

dril down with Temp->Cities

i.e once you click on any of the bar you will get citites having that temperature.

arixooo123
Contributor II

Re: dividing a dimension into some pieces to better show it in a bar chart

Dear Friend,

Let the city be the first dimension. since the number of cities are many, i like to break down them into 2 groups.

i don't think that's the answer of my question.

thanks by the way.

saumyashah90
Valued Contributor

Re: dividing a dimension into some pieces to better show it in a bar chart

If so you will have to add one more column to group your cities...will it be okay?

arixooo123
Contributor II

Re: dividing a dimension into some pieces to better show it in a bar chart

Look at the answer of TerezaGr, a couple of post above your answer. what do you think?