topic Re: FirstSortedValue Rank Formula in App Development

FirstSortedValue Rank Formula

andrewc — Wed, 29 Sep 2021 08:44:58 GMT

=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

Re: FirstSortedValue Rank Formula

Digvijay_Singh — Fri, 01 Oct 2021 03:24:29 GMT

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..

Re: FirstSortedValue Rank Formula

tresB — Fri, 01 Oct 2021 12:58:09 GMT

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.

Re: FirstSortedValue Rank Formula

Digvijay_Singh — Fri, 01 Oct 2021 12:00:53 GMT

Hi @tresB

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.

Re: FirstSortedValue Rank Formula

tresB — Fri, 01 Oct 2021 13:15:38 GMT

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.

Re: FirstSortedValue Rank Formula

Digvijay_Singh — Fri, 01 Oct 2021 12:55:45 GMT

@tresB

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