topic Re: Firstsortedvalue giving NULL when value is tie. in QlikView

Firstsortedvalue giving NULL when value is tie.

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

Hi All,

I was trying to display Top 5 Categories Count in 5 different Text Objects with the firstsortedvalue( with help of Sunny Talwar)

the value are correct but when the Values are tie then its giving NULL.

please see used below expression

=Num(

Count({<ProblemDateType = {'Opened'}, Problem.dv_u_problem_category = {"$(=FirstSortedValue({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.dv_u_problem_category, -Aggr(Count({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.number), Problem.dv_u_problem_category)))"}, Problem.Calendar.Year = {'2018'}>}Problem.number)/

Count({<ProblemDateType= {'Opened'},Problem.Calendar.Year= {'2018'}>} Total Problem.number,'#,##0.##%')

Can any one suggest the solution.

Thanks

Rajesh.

Re: Firstsortedvalue giving NULL when value is tie.

jyothish8807 — Wed, 06 Jun 2018 10:42:00 GMT

Try using concat once:

{"$(=concat(FirstSortedValue({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.dv_u_problem_category, -Aggr(Count({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.number), Problem.dv_u_problem_category)),',')"}

may be modify a bit to get the proper format 'value','value'

Re: Firstsortedvalue giving NULL when value is tie.

Chanty4u — Wed, 06 Jun 2018 10:44:57 GMT

may be try this

=Num(

Count({<ProblemDateType = {'Opened'}, Problem.dv_u_problem_category =

{"$(=FirstSortedValue({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>}

Problem.dv_u_problem_category, -Aggr(Count({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.number),

Problem.dv_u_problem_category,Problem.Calendar.Year)))"}, Problem.Calendar.Year = {'2018'}>}Problem.number)

Count({<ProblemDateType= {'Opened'},Problem.Calendar.Year= {'2018'}>} Total Problem.number,'#,##0.##%')

Re: Firstsortedvalue giving NULL when value is tie.

tresB — Wed, 06 Jun 2018 10:47:55 GMT

Try using DISTINCT like:

....FirstSortedValue( Distinct .....)

Re: Firstsortedvalue giving NULL when value is tie.

Rajesh — Wed, 06 Jun 2018 11:04:48 GMT

HI Tresesco,

I have used FirstSortedValue( Distinct .....) its giving Value but i want to show another tie Value also in another Text object.

Thanks

Rajesh

Re: Firstsortedvalue giving NULL when value is tie.

sunny_talwar — Wed, 06 Jun 2018 11:23:49 GMT

May be try this

=Num(

Count({<ProblemDateType = {'Opened'}, Problem.dv_u_problem_category = {"$(=FirstSortedValue({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.dv_u_problem_category, -Aggr(Count({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.number) + Rank(Only({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.dv_u_problem_category))/1E10, Problem.dv_u_problem_category)))"}, Problem.Calendar.Year = {'2018'}>}Problem.number)/

Count({<ProblemDateType= {'Opened'},Problem.Calendar.Year= {'2018'}>} Total Problem.number,'#,##0.##%')

=Num(

Count({<ProblemDateType = {'Opened'}, Problem.dv_u_problem_category = {"$(=FirstSortedValue({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.dv_u_problem_category, -Aggr(Count({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.number) - Rank(Only({<ProblemDateType = {'Opened'}, Problem.Calendar.Year = {'2018'}>} Problem.dv_u_problem_category))/1E10, Problem.dv_u_problem_category)))"}, Problem.Calendar.Year = {'2018'}>}Problem.number)/

Count({<ProblemDateType= {'Opened'},Problem.Calendar.Year= {'2018'}>} Total Problem.number,'#,##0.##%')

But what if there are 5 categories which have the same rank? How do you expect to see that? I mean there need to be some point where you have to decide that this can't be done in a text box object and move it to a chart or something

Re: Firstsortedvalue giving NULL when value is tie.

Rajesh — Wed, 06 Jun 2018 12:08:52 GMT

Thank you again Sunny

The Values are displaying as expected. i am agree with that we can show Chart in better,But its my client requirement.

Can you tell me that what 1E10 does in expression?

Thanks

Rajesh

Re: Firstsortedvalue giving NULL when value is tie.

sunny_talwar — Wed, 06 Jun 2018 12:11:04 GMT

1E10 is another way of writing 10000000000. I am just dividing Rank portion by a very large number to give it lower importance compared to the count of category.