MDX Question (split from Primer thread)

Post Reply
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

MDX Question (split from Primer thread)

Post by Steve Vincent »

Just starting to work my way thru this, its very helpful indeed :D

I have a question on subsets based on cube data tho. If i have a 7 dim cube and i wrote some MDX to evaluate the value from Dim1 Ele1 with that in Dim1 Ele2 without specifying any of the other dims, does it only "calculate" the set based on top levels for all the other dims? I have some code that's looking at any values that are ±1% between 2 scenarios. At top level its right but as you drill down the set doesn't change, which was the functionality i was looking to replicate. My current MDX is below, does anyone know a way of replicating what I'm aiming for?

Code: Select all

{UNION (
{FILTER (
  {TM1SUBSETALL( [Ops Plan OBS] )}
,  ([ops plan].([Version].[Current Prog Req Supp], [Ops Plan Measures].[Ops Plan Headcount]))
 - ([ops plan].([Version].[Current Agreed Sol], [Ops Plan Measures].[Ops Plan Headcount]))
 > [ops plan].([Version].[Current Prog Req Supp], [Ops Plan Measures].[Ops Plan Headcount]) * 0.01
)}
,
{FILTER (
  {TM1SUBSETALL( [Ops Plan OBS] )}
,  ([ops plan].([Version].[Current Agreed Sol], [Ops Plan Measures].[Ops Plan Headcount]))
 - ([ops plan].([Version].[Current Prog Req Supp], [Ops Plan Measures].[Ops Plan Headcount]))
 > [ops plan].([Version].[Current Prog Req Supp], [Ops Plan Measures].[Ops Plan Headcount]) * 0.01
)}
)}
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Post Reply