2

Top N - List

Hi

 

We have a list of days which are numeric but not a measure. the values range from 0 to 900. We want to only show the element where they are greater than a specific value set by a continuous parameter. Is this possible

 

IE.. If parameter is set to 15 we would want the elements selected to be 15-900 and exclude 0 and 14.

 

Thanks

 

Nick

7 replies

null
    • "making the sophisticated simple"
    • AviPerez
    • 3 yrs ago
    • Reported - view

    Yes - very doable, but you'll need to do it with some PQL/MDX script. The overall solution works regardless of which data technology you use. The example below uses the "PQL" track for SQL databases. You could easily modify the syntax to work in MDX.

    The steps:

    • Create a numeric parameter (global or model) in Formulate. I'm using 'weeks' 1-53, so I created a continuous number parameter from 1 to 50, with a step of 1.
    • Build a list on the target hierarchy from Formulate. Use the "Range List" block from the "Add" menu. Fill it in with hard coded members that represent the "start" and "end" elements.
    • Flip to script mode. You'll see something similar to this. Here my list will build from the first item - week #1 "[transactions].[Dates week].[1]" all the way through to the second item week #53.
    {
    Range(
    [transactions].[Dates week].[1]
    ,[transactions].[Dates week].[53]
    )
     }
    • This is static, and you want it dynamic. So, now we remove the first item "[transactions].[Dates week].[1]" and replace it with "StrToMember" function that will build a member out of the hierarchy [transactions].[Dates week] and a string that will generate the member/element.
    • For the string drag and drop the parameter item (that we created above) from the element tree in the hierarchy and element trees.
    • The formula now looks like this.
    {
    
    Range(
    StrToMember([transactions].[Dates week],[global].#[4049d092-f001-4e34-a859-1d5585030d4c] )
    ,[transactions].[Dates week].[53])
    
    }

    And that's it. Save and use your new list.

    You'll see a slider to choose your starting item and it will auto create a dynamic list of elements.

      • Nicholas_Campbell
      • 3 yrs ago
      • Reported - view

      Avi Perez Worked perfectly many thanks. 

       

      Could ask if following is possible then it finishes it perfectly but if not then that's fine as I have a work around.

       

      The end point of the range isn't always the same number and varies so could be 100 or could be 900. I can select the end member but when model is re-run then that last member could be another value. Is it possible to make the end point of the range dynamic so that looks at last element?

       

      Hopefully explained it well enough. 

      Thanks

       

      Nick

      • "making the sophisticated simple"
      • AviPerez
      • 3 yrs ago
      • Reported - view

      Nicholas Campbell Instead of hard coding the last element, use a function instead. There are various ways of doing it, but the simplest and most direct is the "Tail" function (see help here).

      So, using my example, you'd replace "[transactions].[Dates week].[53]" with this:

      Tail(
       AllMembers([transactions].[Dates week]), 1
      )

      This gets all the members in the week hierarchy (using the "AllMembers" function) and takes the last 1.

      As the hierarchy changes, it will always take the last one - so its now fully dynamic.

      • Nicholas_Campbell
      • 3 yrs ago
      • Reported - view

      Avi Perez Hi

       

      I get the concept but I think I must be doing something wrong as using the following code:

       

      {
        Range(strtomember(
          [Credit_Control_Overdue_Premiums].[Overdue Credit Days],[global].#[cdecc5a3-f154-41ed-8d4f-37dc477421ac])
          ,Tail( AllMembers([Credit_Control_Overdue_Premiums].[Overdue Credit Days][Credit_Control_Overdue_Premiums].[Overdue Credit Days]), 1)
        )
      }

       

      to bring back last member is coming up with "the range function expects a name member expression for the 2 argument. A set of members expression was used?

       

      Any assistance would be greatly appreciated

       

      Many thanks

       

      Nick

      • "making the sophisticated simple"
      • AviPerez
      • 3 yrs ago
      • Reported - view

      Nicholas Campbell You can try wrapping the tail function in GetElement, which will return a member and not a set or list. Another idea is to simply remove the tail and replace it with the last element function. See help.

    • Marais_Kruger
    • 3 yrs ago
    • Reported - view

    In the next version we introduced regular expressions where you would be able to do this with this with a custom regular expression.

      • Marais_Kruger
      • 3 yrs ago
      • Reported - view

      Sorry, see now you meant model elements. Ignore previous answer from me.

Content aside

  • Status Answered
  • 2 Likes
  • 3 yrs agoLast active
  • 7Replies
  • 51Views
  • 4 Following