topic Re: Bar chart question sum or count ? in QlikView

Bar chart question sum or count ?

Tue, 16 Sep 2014 18:23:52 GMT

Hi,

Im trying to build a bar chart that will separate the peoples age in different categories

here is my data

NAME	AGE
pierre	45
jean	23
peter	13
michael	11
albert	8
patricia	5

I want my bar chart to represent how many people are under 7 / between 7 and 14 / upper than 14

I cant get to find the right expression to do so.

Can someone help , im sure it is not so complicated but im a very beginner

Re: Bar chart question sum or count ?

Anonymous — Tue, 16 Sep 2014 19:19:23 GMT

Hi,

You need to use intervalmatch functionality :

T1:

load * inline

[Name, Age

pierre,45

jean,23

peter,13

michael,11

albert,8

patricia,5];

Interval:

Load * inline

[Start, End, Description

0,6, <7

7,13, 7-14

14,99, >14];

BridgeTable:

IntervalMatch (Age)

Load distinct Start, End Resident Interval;

Re: Bar chart question sum or count ?

maxgro — Tue, 16 Sep 2014 20:10:31 GMT

see attachment

Re: Bar chart question sum or count ?

Thu, 18 Sep 2014 19:11:28 GMT

Hello,

Thanks for your answer massimo.

What if my name name colum contains 10000 records?

I was thinking of building a chart referencing the table and for each bar of the chart make a formula without using alternative table,

i will write it in natural language as i am not sure of the qlikview

so i wanna see the number of people on the vertical axis

on the horizontal the bar with the number of people under 7 years for example

so for this write something like

if age under 7 then count the number of people and display it : i would label it <7 years

if age between 7 and 15 then count number of people etc...

I am just not sure that your solution is adaptable to my scale of people, or is it?

Thanks

Re: Bar chart question sum or count ?

Thu, 18 Sep 2014 19:16:22 GMT

do you see any way to do it without a bridge table? directly in the formula of the chart ?

Re: Bar chart question sum or count ?

nagaiank — Thu, 18 Sep 2014 19:37:17 GMT

You may use script similar to the one below. Use AgeGroup as Dimension and Count(NAME) as Expression in a bar chart.

Data:

LOAD *,If(AGE < 7,'Under 7',If(AGE > 14,'Over 14','7 - 14')) as AgeGroup;
LOAD * inline [
NAME, AGE
pierre, 45
jean, 23
peter, 13
michael, 11
albert, 8
patricia, 5
];

Re: Re: Bar chart question sum or count ?

maxgro — Thu, 18 Sep 2014 20:04:57 GMT

you can manage with class function and if as already posted (in the attachment 10000 different names)

you can also calculate every bar with an expression (last sheet in the attachment)

Re: Bar chart question sum or count ?

jagan — Fri, 19 Sep 2014 03:59:49 GMT

Hi,

Try like this in script

Data:

LOAD *,

If(Age > 14, 'Over 14',

If(Age >= 7, '7 - 14', 'Under 7')) as AgeGroup;

LOAD * inline [

Name, Age

pierre, 45

jean, 23

peter, 13

michael, 11

albert, 8

patricia, 5

];

Regards,

Jagan.