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: 
Not applicable

Linear Table

Hi everyone,

I'm new on QLIK so I have some problem.

I've a question for you.

I did a Linear Table with 3 fields (Name,Surname,Score). I've 30 results (lines or record).

I'd like to Sum only the first 5 score and put it in a new column called "Best 5 scores".

How can I do an expression that sum only the first 5 records ??

Thank you so much

JP

1 Solution

Accepted Solutions
vgutkovsky
Master II
Master II

See attached for one possible solution.

-Vlad

View solution in original post

27 Replies
vgutkovsky
Master II
Master II

The simplest way to do this is via sorting. So, if your chart is a straight table, go to Sort, select Score, change the sort to Numeric Value descending and promote it to the top. Then, in Presentation, select "Max Number" and set it to 5.

Regards,

Vlad

Not applicable
Author

HI Vlad,

first of all thank you for your reply.

I have to tell you that I already tried that way. It's a good way to show only the best five results but if I try to sum all the rows (about score) I obtain a wrong result because it sums not only the five rows but all the rows.

Thank you so much

vgutkovsky
Master II
Master II

For something like that, I think you would get the best performance out of using the rank function in set analysis. So, first create a field called "Full Name" in the script that is just this:

Name & ' ' & Surname as [Full Name]

Then you can either use this Full Name as your new table dimension or leave it as 2 dimensions in the chart, Name and Surname. Replace your expression with the following:

sum({<[Full Name]={"=rank(total sum(Score))<=5"}>} Score)

You don't need to set any max-visibility settings in Presentation anymore.

Regards,

Vlad

Not applicable
Author

Hi Vlad,

I'm going to do it immediately.

I'll tell you later about it.

THANK YOU SO MUCH

Not applicable
Author

Hi Vlad,

unfortunately it doesn't work.

I did the steps you suggest me.

The result is:

Full Name      Score      Expression

Paul Berry      823,14      823,14

Paul Berry      812,11      812,11

....

Expression = sum({<[Full Name]={"=rank(total sum(Score))<=5"}>} Score)

THANK YOU SO MUCH

rohit214
Creator III
Creator III

hi

i attached example plz find it

Not applicable
Author

Hi Rohit,

First of all THANK YOU.

Unfortunately, if I well understood the example, it doesn't help me.

You take the max(UnitPrice) of all customers. So you have the first 5 Customers with the max Price.

My problem is different. I'll show you with an example:

Full Name               Score

Paul Barry               790,15

Paul Barry               801,13

Paul Barry               847,12

Paul Barry               615,77

Paul Barry               864,98

Paul Barry               802,56

Paul Barry               823,78

My goal is obtain a results like this:

Full Name               Score

Paul Barry               864,98

Paul Barry               847,12

Paul Barry               823,78

Paul Barry               802,56

Paul Barry               801,13

total                      4139,57

OR

Full Name               Score               Total

Paul Barry               864,98              4139,57

Paul Barry               847,12

Paul Barry               823,78

Paul Barry               802,56

Paul Barry               801,13

I hope it helps to understand my problem

THANK YOU SO MUCH

vgutkovsky
Master II
Master II

Please clarify, what is the dimension of your chart? If it's just Name, then why would you expect to see the same name 5 times?

Not applicable
Author

I'll try to clarify to you my problem.

The dimensions in my chart (table) are:

FullName, Dinstance, Stroke, Time, Score

So I have my table built in this way

FullName,   Dinstance, Stroke,                  Time,     Score

Paul Berry  50              FreeStyle              22"30     823,78

Paul Berry  50              BackStroke           26"30     803,78

Paul Berry  100            Breaststroke          57"30     893,78

Paul Berry  50              Butterfly                25"30     623,78

Paul Berry  100            Individual Medley   59"30      798,78

Paul Berry  200            Free Style            1'43"30    855,78

Paul Berry  50              Breaststroke         30"30      878,78

Paul Berry  50              FreeStyle              23"10     810,78

What I'm expecting to have are the best five results based on the Score.

I can't have more than one row with the same dinstance and stroke per Athlet.

So, if I have ( 50 FreeStyle 22"30 823,78) and (50 FreeStyle 23"10 810,78) I have to take (50 FreeStyle 30"3022"30 823,78).

About this I built this expression (with the label "BestFive") :

Aggr(Max(Score),Dinstance,Stroke)

it works perfectly.

So my results up to now is:

FullName,   Dinstance, Stroke,                  Time,     BestFive

Paul Berry  50              FreeStyle              22"30     823,78

Paul Berry  50              BackStroke           26"30     803,78

Paul Berry  100            Breaststroke          57"30     893,78

Paul Berry  50              Butterfly                25"30     623,78

Paul Berry  100            Individual Medley   59"30      798,78

Paul Berry  200            Free Style            1'43"30    855,78

Paul Berry  50              Breaststroke         30"30      878,78

Paul Berry  50              FreeStyle              23"10     -                   

What I would like to have is:

FullName,   Dinstance, Stroke,                  Time,     BestFive

Paul Berry  100            Breaststroke          57"30     893,78

Paul Berry  50              Breaststroke         30"30      878,78

Paul Berry  200            Free Style            1'43"30    855,78

Paul Berry  50              FreeStyle              22"30     823,78

Paul Berry  50              BackStroke           26"30     803,78

Total                                                                     4300,00

I'm sorry it isn't easy to me to explain it. I hope it's clear.

Again THANK YOU SO MUCH for you help.

Best regards,

Giampiero