Grouping within lower and upper limit

[tracycrown]

Dear All

I am trying to group the average score (last column) of each student in Table A to Table B but I could not get the correct answer, please help.

Table A:

Student	Finance	English	Law	Average
C1	18.0%	19.0%	23.0%	20.0%
C2	30.0%	45.0%	52.0%	42.3%
C3	40.0%	30.0%	52.0%	40.7%
C4	50.0%	30.0%	25.0%	35.0%
C5	60.0%	67.0%	63.0%	63.3%
C6	70.0%	70.0%	70.0%	70.0%
C7	80.0%	83.0%	88.0%	83.7%
C8	90.0%	92.0%	93.0%	91.7%
C9	100.0%	92.0%	95.0%	95.7%
C10	60.0%	73.0%	75.0%	69.3%

Table B :

Lower Score	Upper Score	# of Student
1	20
21	40
41	60
61	80
81	100

Correct Answer :

Lower Score	Upper Score	# of Student
1	20	1
21	40	1
41	60	2
61	80	3
81	100	3

Thank you

Tracy

[malini_qlikview]

You can use Class function in the calculated dimension as below

Dimension : =class(Aggr(Round(Avg(Score)*100),Student),20,'X',1)

Expression : =Count(Distinct Student)

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

=Aggr(Dual(SubField(Class(Ceil(Avg(Score)*100),20,'x',1),' <= x < ',1)&' < Score <= '&(SubField(Class(Ceil(Avg(Score)*100),20,'x',1),' <= x < ',2)-1)
          ,SubField(Class(Ceil(Avg(Score)*100),20,'x',1),' <= x < ',1))
      ,Student)

hope this helps

regards

Marco

[tracycrown]

Dear Macro Wedel

Thank you very much for your help, the answer is exactly the same as what I want but difficult for me to understand the logic as I am new to Qlikview.

If the score range is uneven (eg. 1-25, 26-40,41-60 etc) in the worksheet, then your method might not work. Please advise is there a way to read directly from worksheet ?.

Thank You, Tracy

[tracycrown]

Dear Malini Chandrasekar

Thank you so much for your quick response.

Your answer is simple but incorrect, please refer to the excel worksheet.

Thank you, Tracy