7 Replies Latest reply: Feb 16, 2018 12:23 PM by Petter Skjolden

# Group the numbers

Hello,

How do i group the numbers in Qlik sense to create a table & a graph.

I have different numbers but i want to group these numbers in one order.

I need to group these numbers in first column like this

What formula should i use and how can i get it in a table like above and also in a bar graph or line graph?

Sachin

• ###### Re: Group the numbers

Hi,

you have two methods to Group your data:

1. on the script with a Group by

2. on the front-end with aggregation functions like Sum, Count, Aggr......

it depends on what you need

• ###### Re: Group the numbers

Hello Youssef,

What if i use the front-end

can you give an example

columnA      ColumnB

45                    1

30                    2

100                   5

75                    1

12                    13

5                         1

25                    18

i need something like to be grouped in Qlik sense like this

columnA          columnB

0-25                    32

25-50                    2

50-75                    1

75-100                   5

How can i get this

• ###### Re: Group the numbers

you can use this as a dimension:

=if(columnA>=0 and columnA<=25,'0-25',

if(columnA<=50 and columnA>=25,'25-50',

if(columnA<=75 and columnA>=50,'50-75',

if(columnA<=100 and columnA>=75,'75-100'))))

you can use Class() but you will be limited on the result

• ###### Re: Group the numbers

For grouping you can use the class function.

Class Function Example

• ###### Re: Group the numbers

Here is a way to do it if you need non-uniform bins/bucket sizes. The IntervalMatch() function can be used along with a interval table that specify the different binsizes:

```CARGO:
ID, Weight,Neg,Op,Pos
1, 30,1
2, 30,,,1
20, 99,1
3, 100,1
4, 50,1,,1
5, 490,,1
6, 300,1
7, 299,,1
8, 101,1
9, 200,,1,
];

GROUPS:
From,To,WeightGroup
0,100,100 KG
101,200,200 KG
201,300,300 KG
301,499,500 KG
];

INNER JOIN
IntervalMatch( Weight )
From, To
RESIDENT
GROUPS;

LEFT JOIN (GROUPS) LOAD * RESIDENT CARGO;
DROP TABLE CARGO;
RENAME TABLE GROUPS TO CARGO;

// DELETE THE NEXT LINES IF YOU NEED THE CARGO TABLE AND NOT JUST THE SUMMARY
GROUPED:
WeightGroup,
Sum(Neg) AS Negative,
Sum(Neg)/Count(WeightGroup) AS NegRatio,
Sum(Op) AS Open,
Sum(Op)/Count(WeightGroup) AS OpenRatio,
Sum(Pos) AS Positive,
Sum(Pos)/Count(WeightGroup) AS PosRatio,
Count(WeightGroup) AS Total
RESIDENT
CARGO
GROUP BY
WeightGroup;
DROP TABLE CARGO;
```