Group x-axes values

[#1]

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!

[Anonymous]

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.

[#3]

Thank you for your fast and correct answer!

I knew it would have been obvious...

[#4]

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)

[Anonymous]

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

or

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.

[#6]

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

[Anonymous]

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

[#8]

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

[Anonymous]

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.

[Anonymous]

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?