Formulate: Comine Standard List with a Measure
I'm not sure if I'm just "standing on the tube" or if my idea can't be realized, so I hope for your support:
In formulate, I want to combine the function of a "standard list" with a measure:
-> I want to build a "custom measure" that I can use separately from my other measures inside the drop zone in Discover, but it should only react (show values) if any of the linked elements from the standard list are included in the report selection.
Or, maybe from another perspective, I am looking for a similar function to the "Data Point" but with the possbility to have a multi-selection rather than a single selection for the hierarchy.
Any ideas :)?
Thanks and best regards
Have you tried using aggregation block? It allows you to combine list and measure (data point):
First, as a general concept, the PQL and MDX languages are very flexible in achieving almost any logical formulation needed. Sometimes, the graphical tools don't facilitate this flexibility so you need to use the scripting mode - which is not bound by the UI's limitations. Other times the path to resolving your analytical problem is not direct, so you need to do a few extra steps. And then, unfortunately, there are rare instances where something is impossible. We like to monitor all three headaches to see if we can make it simpler, easier or possible.
Now on to your question: The 'data point' is a logical element that can only ('legally') refer to a single item from any dimension at a time. To achieve the multiple items outcome you are after, you need to build a set of those items and aggregate them into a custom member (or formula) - what Uros is suggesting. Then, select this single custom member in your final data point - in the second calculation - to achieve the outcome you are after. Its indirect, but achievable.
To further enhance your calculation, consider building a parameter of the items you want. Then build the customer member aggregate using the parameter instead. This will allow you to dynamically change the aggregated element in the final data point from the report on-the-fly.
Hi Avi, hi Uros,
thanks for your suggestions! Sounds like that - again- we can count on the powerful and flexible functions Pyramid is offering :).
I will try out your solution within the next days and report back here.
your solution worked perfectly fine, thank you!