Firstsortedvalue giving NULL when value is tie.

[Rajesh]

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.

[jyothish8807]

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'

[Chanty4u]

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.##%')

[tresesco]

Try using DISTINCT like:

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

[Rajesh]

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

[sunny_talwar · Accepted solution]

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

[Rajesh]

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

[sunny_talwar]

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.