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
-
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.
-
In the next version we introduced regular expressions where you would be able to do this with this with a custom regular expression.