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: 
tracycrown
Creator III
Creator III

Grouping within lower and upper limit

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:

   

StudentFinanceEnglishLawAverage
C118.0%19.0%23.0%20.0%
C230.0%45.0%52.0%42.3%
C340.0%30.0%52.0%40.7%
C450.0%30.0%25.0%35.0%
C560.0%67.0%63.0%63.3%
C670.0%70.0%70.0%70.0%
C780.0%83.0%88.0%83.7%
C890.0%92.0%93.0%91.7%
C9100.0%92.0%95.0%95.7%
C1060.0%73.0%75.0%69.3%

 

Table B :

   

Lower ScoreUpper Score# of Student
120
2140
4160
6180
81100

Correct Answer :

   

Lower ScoreUpper Score# of Student
1201
21401
41602
61803
811003

Thank you

Tracy

1 Solution

Accepted Solutions
MarcoWedel

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)

QlikCommunity_Thread_185483_Pic1.JPG

QlikCommunity_Thread_185483_Pic2.JPG

QlikCommunity_Thread_185483_Pic3.JPG

hope this helps

regards

Marco

View solution in original post

4 Replies
malini_qlikview
Creator II
Creator II

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

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)

QlikCommunity_Thread_185483_Pic1.JPG

QlikCommunity_Thread_185483_Pic2.JPG

QlikCommunity_Thread_185483_Pic3.JPG

hope this helps

regards

Marco

tracycrown
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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