How to find out the second smallest element in eazybi

Questions & Answers
, ,
1

Hello, we have on Excel formula Small (A1:A100;(0,8*2)+1). Is there any alternatives in Eazy Bi for this function?

Thank you, for your help!

2

Hi, @menshikova.olesya678

Welcom to the eazyBI community.

We don’t have the SMALL function, but we can define a new calculated measure: Calculated measures and members

First, we need to take a set of members and then Filter the relevant items that have the value. Order the list and then take the first two using the Head function. And take the last item from those two with Tail.

Example where I want to know the 2nd smallest of measure [Issue Story Points]:

Sum(
   Tail(
      Head (
         Order(
         --all issues in the project
         Filter(
         -- set of members in scope
        [Issue].CurrentMember.Siblings,
        -- condition for relevant items
        [Measures].[Issue Story Points]>0),
        [Measures].[Issue Story Points],
     ASC
     ),
   2),
  1),
[Measures].[Issue Story Points]
)

Kindly,
Ilze