FirstSortedValue Rank Formula

[andrewc]

=FirstSortedValue(

                                        {<[MHS_SP.Abandoned]={'1'}, [MHS_SP.TimeBeforeAbandoned]={">=20"}>}
                                        [MHS_SP.TimeBeforeAbandoned],
                                        -([MHS_SP.TimeBeforeAbandoned] + ([MHS_SP.SessionStartAestTime]/1E10)),
                                        24000)

So I am having trouble with the last variable 'Rank' (24000). The above runs run and gives the correct answer. However, I want to convert the Rank value to a formula. The below replacement formula tested returns the same value (24000), but as soon as I drop it into the FirstSortedValue I get NULL.

=FirstSortedValue(

                                        {<[MHS_SP.Abandoned]={'1'}, [MHS_SP.TimeBeforeAbandoned]={">=20"}>}
                                        [MHS_SP.TimeBeforeAbandoned],
                                        -([MHS_SP.TimeBeforeAbandoned] + ([MHS_SP.SessionStartAestTime]/1E10)),
                                        round(Sum([MHS_SP.Abandoned]) * 0.8) )

In fact, it has nothing to do specifically with the above formula. Any metric formula returns NULL, even if I use COUNT(1).

=FirstSortedValue(

                                        {<[MHS_SP.Abandoned]={'1'}, [MHS_SP.TimeBeforeAbandoned]={">=20"}>}
                                        [MHS_SP.TimeBeforeAbandoned],
                                        -([MHS_SP.TimeBeforeAbandoned] + ([MHS_SP.SessionStartAestTime]/1E10)),
                                        count(1) )

As soon as I convert it to 1 - it works.

Thoughts?

Andrew

[Digvijay_Singh]

I just tried few things on the 3rd parameter, looks like firstsortedvalue expect 3rd parameter to have numeric value before it starts evaluating it, so may be you try enclosing your 3rd parameter inside $(=).

firstsortedvalue (Product, UnitPrice, $(=Count(1))) worked so may be you try on similar lines..

[tresesco · Accepted solution]

I would interpret this little differently. 

3rd parameter expects numeric value that's true - but it doesn't have to be a non-expression. It has to be an expression that returns single number. The issue with your expression is the scope of aggregation and that is actually producing multiple numbers. And you can't have multiple ranks passed in here as 3rd parameter. 

If you put Count(total ...) or Sum(total...) you would see it is working - because it returns single value. $(=..) solution would work because it gets evaluated outside your aggregation scope of the expression and generates one number. 

Hope this makes sense.

[Digvijay_Singh]

Hi @tresesco 

Thanks, missed looking from that angle, but I was wondering why Count(1) doesn't work, if I put that in a text box it shows value 1, generally I take it as resulting single value if something shows up in text box. 

[tresesco]

Hi @Digvijay_Singh ,

Let us put that our context is 'expression in KPI object/text box; no chart with dimension'.

Had this Count(...) been used in other function like, Max( Sales, Count(...)) - it would work, because, as you said count(...) in this case would return single value. However, I would assume that FirstSortedValue() is unlike all other aggregation functions (I really don't know how under-the-hood algorithm is working - it is just my logical inference you can say). FirstSortedValue() has a dimension mentioned as a parameter against which the the ranking has to happen and based on the sorted result - it gives the output. The  2nd parameter we pass in this function is nothing but a kind of virtual aggregation dimension. 

My bet is that - if you are putting an expression as a third parameter for rank position - it is going to be evaluated based on the same virtual dimension -  and that is the reason it generates possible multiple values.       

I.e. -  scope of function evaluation is the key here.

Update : I was playing around with it a little more, and seems that actually both the 1st & 2nd parameters play role in virtual aggregation.

[Digvijay_Singh]

@tresesco 

Now I get it. Great! Thanks for the clarification!