Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Hi Vlad,
I'm going to do it immediately.
I'll tell you later about it.
THANK YOU SO MUCH
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
hi
i attached example plz find it
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
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?
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