Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Thank you in advance
Sachin
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
For grouping you can use the class function.
Hi,
you can't Group data using Class() function
It allows you to assign classes to your grouped data
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
Yes, I wrote it wrong. I meant he could use that function to assing a name a label to the bucket.
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
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:
LOAD * INLINE [
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:
LOAD * INLINE [
From,To,WeightGroup
0,100,100 KG
101,200,200 KG
201,300,300 KG
301,499,500 KG
];
INNER JOIN
IntervalMatch( Weight )
LOAD
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:
LOAD
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;