topic Group x-axes values in QlikView

Group x-axes values

Thu, 22 Dec 2011 13:52:21 GMT

Dear Qlikview community,

I'm trying to create a bar chart where I group the x-axis values. However I do not succeed very well at that. How can I group my x-axis values?

Example:

I have data like:

Age Length

5 1000

12 320

3 120

1 300

I want to display this on a graph with a grouping like 0-5; 5-10; ... The result should be:

Age Length

5-9 1000

10-14 320

0-4 420

What is the best way to achieve this?

Thanx!

Re: Group x-axes values

Anonymous — Thu, 22 Dec 2011 14:11:17 GMT

Hi There,

You can do this by using a Calculated Dimension.

Clikc on Add Calculated Dimesion and use something like:

=IF (AGE <= 4 , '0-4' ,

IF (AGE >= 5 and AGE <= 9, '5-9',

IF (AGE >= 10 and AGE <= 14, '10-14',

'15 +')))

Where AGE is your fieldname.

Good Luck,

Dennis.

Group x-axes values

Thu, 22 Dec 2011 15:13:35 GMT

Thank you for your fast and correct answer!

I knew it would have been obvious...

Group x-axes values

Thu, 22 Dec 2011 15:19:48 GMT

Now how do I make sure that age categories that have no y-values do show up on the x-axis? (with an y-value of 0)

Re: Group x-axes values

Anonymous — Thu, 22 Dec 2011 16:02:27 GMT

There are several option for that.

If the age value is '0' (filled) then you have to do nothing ( '0' is smaller then 4 so it will be in your first group)

If it has no value (this is a big differents) then you have to deside what this value should be and how you want it to be displayed in your charts.

Option a: Fill these values in your loading script:

IF (ISNULL(AXE), '0' , AGE) as AGE, // This will add them to the 0-4 group

IF (ISNULL(AXE), 'No Value' , AGE) as AGE,

Then change the calculated dimension to

=IF (AGE = 'No Value', 'Unknown',

IF (AGE <= 4 , '0-4' ,

IF (AGE >= 5 and AGE <= 9, '5-9',

IF (AGE >= 10 and AGE <= 14, '10-14',

'15 +'))))

Option b: Use the ISNULL() funtion in your calculated dimension

=IF ISNULL(AGE), 'Unknown',

IF (AGE <= 4 , '0-4' ,

IF (AGE >= 5 and AGE <= 9, '5-9',

IF (AGE >= 10 and AGE <= 14, '10-14',

'15 +'))))

I hope this is usefull for you.

Good Luck,
Dennis.

Re: Group x-axes values

Thu, 22 Dec 2011 17:08:27 GMT

Sorry I must have explained it wrong. What I meant is that some categories do not show up on the x-axis. Like this dataset:

0-4: 10

5-9: 20

10-14: no data

15-19: 10

Then on the chart the 10-14 category will be skipped. Do you know a way to display it even though it's empty? Thank you very much!!

Re: Group x-axes values

Anonymous — Thu, 22 Dec 2011 18:16:29 GMT

Did you check the box "Show all values" on your dimension tab?

Re: Group x-axes values

Thu, 22 Dec 2011 19:07:15 GMT

Yes, that's what puzzled me, it doesn't matter if it's checked or not, the values don't show.

Re: Group x-axes values

Anonymous — Thu, 22 Dec 2011 19:19:15 GMT

Now I think about it, it make sence.

In the calculated dimension it says:

IF (AGE >= 10 and AGE <= 14, '10-14',

In other words; create a value/dimension (10-14) when the value of the field AGE is between 10 en 15.

But there is no value between 10 and 15 so the value/dimension will not be created.

Re: Group x-axes values

Anonymous — Thu, 22 Dec 2011 19:35:22 GMT

Try this:

Create these groups not in your dimension but already in your script.

You should be able to do this by adding an Inline load to your data

JOIN (YourData)

Load * Inline [

Age, AgeGroup

1, '1-5'

2, '1-5'

3, '1-5'

4, '1-5'

5, '1-5'

6, '6-10'

7, '6-10'

8, '6-10'

9, '6-10'

10, '6-10'

11, '11-15'

12, '11-15'

13, '11-15'

14, '11-15'

15, '11-15'

16, '16-20'

17, '16-20'

18, '16-20'

19, '16-20'

20, '16-20'];

this will create your grouped dimension even if there is no value in your original AGE field.

Is this something that could work for you?

Re: Group x-axes values

Thu, 22 Dec 2011 20:53:20 GMT

SMART! That did the trick

Thank you very much!!

Re: Group x-axes values

Mon, 10 Dec 2012 11:59:45 GMT

After all this time I found another solution to the very first topic I've created. There exists a function 'class()' which can group dimensions like this without altering the script code.

Re: Group x-axes values

Aleman — Tue, 21 Jul 2020 20:52:07 GMT

Awesome, worked for me.

Thanks!