Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expressions and Dimensions in Bar Charts

Hi,

I am very new to qlikview. I have a very straightforward excel data file that I would like to analyse, example below:

CountryRegionAge (0-20 years)Age (21-40)Age (41 +)
EnglandYorkshire10%20%70%
EnglandCounty Durham5%30%65%
ScotlandFife10%40%50%
Northern IrelandBelfast20%20%60%

I would like to create a bar chart with the age variables (last 3 columns on the right) as the X axis and the percentage data as the values in the chart (y axis 0% - 100%). I loaded the data into qlikview in a standard way and created 3 seperate expressions for the Age Bands (which can be seen in the legend). I then used list boxes for country and region so that these can be selected and the bar chart changes - lovely. A couple of questions:

  1. In this example do I need a dimension?
  2. How can I make the Age categories in the above example appear in the x axis labels?

Thanks

1 Solution

Accepted Solutions
Nicole-Smith

In your script, do the following:

TableName:

CrossTable(Age, Proportion, 2)

LOAD Country

          Region

          Age (0-20 years)

          Age (21-40)

          Age (41+)Age (0-A20 yeaAs)

FROM YourFile.csv;

View solution in original post

7 Replies
Not applicable
Author

Hope this helps!

class( expression, interval [ , label [ , offset ]] )

Creates a classification of expressions. The bin width is determined

by the number set as interval. The result is shown as a<=x<b, where

a and b are the upper and lower limits of the bin. The x can be

replaced by an arbitrary string stated in label. 0 is normally the

default starting point of the classification. This can be changed by

adding an offset.

Examples:

class( var,10 ) with var = 23 returns '20<=x<30'

class( var,5,'value' ) with var = 23 returns '20<= value <25'

class( var,10,'x',5 ) with var = 23 returns '15<=x<25'

Not applicable
Author

Sorry, I suspect I have made the mistake of trying to make this very complicated and the actual  issue is the way I am loading the table. How do I make the above excel flat table load into a long thin Qlikview table that would look something like this (begining of table shown as example)?

CountryRegionAgeProportion
EnglandYorkshire0-2010%
EnglandYorkshire21-4020%
EnglandYorkshire41+70%
EnglandCounty Durham0-205%
EnglandCounty Durham21-4030%
EnglandCounty Durham41+65%




So the 3 age columns become a new column and the data is all placed into the final new end column?

Thanks,

Nicole-Smith

In your script, do the following:

TableName:

CrossTable(Age, Proportion, 2)

LOAD Country

          Region

          Age (0-20 years)

          Age (21-40)

          Age (41+)Age (0-A20 yeaAs)

FROM YourFile.csv;

Not applicable
Author

Please do it as suggested by Nicole! That will work out...

Not applicable
Author

Thanks folks, that is really haelpful and works!

I just need to work out exactly what has happened in the following line.

CrossTable(Age, Proportion, 2)

Age and Proportion are the new variables. What exactly is the number 2 doing, To my mind it takes every variable after the first two and pivoting the data into the new format is this correct and is there a better explanation?


Nicole-Smith

What you're thinking is correct.  The 2 means keep the first 2 columns the way that they are, and then pivot the rest of the columns into two new columns named Age and Proportion.

Not applicable
Author

Thanks, that is a much clearer way of putting it. I now have a crosstable \ fill question but will put this in a new discussion.