topic Re: FirstSortedValue Not showing First "TOP USER" in textbox in QlikView

FirstSortedValue Not showing First "TOP USER" in textbox

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

FirstSortedValue Not showing First "TOP USER" in textbox when two users have the same value. For example if Tom has 10 sessions and Harry has 9, my top user will display Tom, which is correct. If Tom and Harry both have 10, it doesn't show no one.

I've come across this before but it will take the first sorted by alphabet, how can i sort this out to only show one user?

Regards

='TOP USER: ' & FirstSortedValue([Name], -aggr(count( Session), [Name]))

Re: FirstSortedValue Not showing First "TOP USER" in textbox

sunny_talwar — Fri, 06 Jan 2017 11:59:35 GMT

May be this:

='TOP USER: ' & FirstSortedValue([Name], -aggr(Count( Session) + Rank([Name])/1E10, [Name]))

not sure if it needs to be + or -, try both

='TOP USER: ' & FirstSortedValue([Name], -aggr(Count( Session) - Rank([Name])/1E10, [Name]))

Re: FirstSortedValue Not showing First "TOP USER" in textbox

Anonymous — Fri, 06 Jan 2017 12:08:02 GMT

That's giving me an error in the expression

Re: FirstSortedValue Not showing First "TOP USER" in textbox

vinieme12 — Fri, 06 Jan 2017 12:12:58 GMT

it may be because you have more than one customer with the same weight / rank , FirstSortedvalue will return null in that case

Re: FirstSortedValue Not showing First "TOP USER" in textbox

Anonymous — Fri, 06 Jan 2017 12:14:29 GMT

I know that bit, I'm trying to resolve how to sort it, possibly by sorting by alphabet.

Re: FirstSortedValue Not showing First "TOP USER" in textbox

sunny_talwar — Fri, 06 Jan 2017 12:15:27 GMT

Apologizes, I forgot to add closing parenthesis for Rank function

='TOP USER: ' & FirstSortedValue([Name], -aggr(Count( Session) + Rank([Name])/1E10, [Name]))

='TOP USER: ' & FirstSortedValue([Name], -aggr(Count( Session) - Rank([Name])/1E10, [Name]))

Re: FirstSortedValue Not showing First "TOP USER" in textbox

Anonymous — Fri, 06 Jan 2017 12:43:34 GMT

Thanks Sunny T, yet again

Happy New Year Guys

Re: FirstSortedValue Not showing First "TOP USER" in textbox

sunny_talwar — Fri, 06 Jan 2017 12:44:29 GMT

Happy New Year!!

Re: FirstSortedValue Not showing First "TOP USER" in textbox

trdandamudi — Fri, 06 Jan 2017 13:54:31 GMT

Sunny,

You are dividing Rank([Name]) / 1E10 and what does 1E10 refers to ?

Thanks

Re: FirstSortedValue Not showing First "TOP USER" in textbox

sunny_talwar — Fri, 06 Jan 2017 13:56:57 GMT

1E10 = 1/10,000,000,000 (1 divided by 10,000,000,000)

Re: FirstSortedValue Not showing First "TOP USER" in textbox

Anonymous — Fri, 06 Jan 2017 15:35:18 GMT

How does this work? if A=1 and B=1? 1 Being "1" not 0.9178181 or 1.00000002525, Would the two 1's not keep going in parallel meaning they will always be the same?

Re: FirstSortedValue Not showing First "TOP USER" in textbox

sunny_talwar — Fri, 06 Jan 2017 15:49:48 GMT

Name itself will be ranked differently. For example

B = 1

A = 2

Re: FirstSortedValue Not showing First "TOP USER" in textbox

Anonymous — Fri, 06 Jan 2017 15:53:57 GMT

So if we have two John Smith's both with number 1, we are screwed? so it's fair to say this isn't 100% bulletproof? Don't get me wrong, I'm happy with the result, but I should be aware it's not 100% or is it?

Re: FirstSortedValue Not showing First "TOP USER" in textbox

sunny_talwar — Fri, 06 Jan 2017 15:56:08 GMT

Here is your bullet proof

='TOP USER: ' & FirstSortedValue(DISTINCT [Name], -aggr(Count( Session) + Rank([Name])/1E10, [Name]))

='TOP USER: ' & FirstSortedValue(DISTINCT [Name], -aggr(Count( Session) - Rank([Name])/1E10, [Name]))

Since the name is the same, doesn't matter which John Smith shows up, right

Re: FirstSortedValue Not showing First "TOP USER" in textbox

sunny_talwar — Fri, 06 Jan 2017 15:56:32 GMT

Or use Employee ID to differentiate between John Smiths

Re: FirstSortedValue Not showing First "TOP USER" in textbox

Anonymous — Fri, 06 Jan 2017 16:07:52 GMT

Hope I didn't offend you by saying bulletproof, but I'm sure you know where i'm coming from.

Re: FirstSortedValue Not showing First "TOP USER" in textbox

Anonymous — Fri, 06 Jan 2017 16:08:34 GMT

It's easier to read names to ID numbers when you got 5k employees.

Re: FirstSortedValue Not showing First "TOP USER" in textbox

sunny_talwar — Fri, 06 Jan 2017 16:09:54 GMT

I absolutely don't feel offended... I am glad you are thinking above and beyond.... I absolutely absolutely encourage you to do that

UPDATE: Made sure the above statement read right

Re: FirstSortedValue Not showing First "TOP USER" in textbox

sunny_talwar — Fri, 06 Jan 2017 16:10:58 GMT

Agreed!! but use that for sorting

='TOP USER: ' & FirstSortedValue(DISTINCT [Name], -aggr(Count( Session) + Rank(EmpID)/1E10, [Name]))

='TOP USER: ' & FirstSortedValue(DISTINCT [Name], -aggr(Count( Session) - Rank(EmpID)/1E10, [Name]))

Re: FirstSortedValue Not showing First "TOP USER" in textbox

Anonymous — Fri, 06 Jan 2017 16:16:17 GMT

I did and it works a treat.