May 17, 2011 at 1:03 am
Hi everybody.
I have a problem to execute a MDX query.
I have a hierarchy (from particular to general): Sector->Coil->Campaign, and a mesure KPI4 for each Sector.
A coil is composed by 20 Sector, so I would to aggregate (applying the AVG function) on Coil level, but excluding the first and last Sector of each coil (i.e.
number 1 and 20).
What is the right MDX query?
regards
nico
May 17, 2011 at 10:19 am
Hey Nico,
After reading this post (and your other) I would be leaning towards using an MDX Script within the cube definition itself to perform this. Using SCOPE statement/s and an IIF statement I think you could achieve this. It would though, mean that *veryone* accessing the cube would get the measure returned in that format (ie sum at lower level, avg minus start and end at upper).
Steve.
May 18, 2011 at 8:31 am
Something like this?
WITH
MEMBER
[Measures].[Custom] AS
AVG(EXCEPT([Dimension].[HIERARCHY].CURRENTMEMBER.CHILDREN, {[Dimension].[HIERARCHY].CURRENTMEMBER.FIRSTCHILD,[Dimension].[HIERARCHY].CURRENTMEMBER.LASTCHILD }), [Measures].[Your Measure])
SELECT
{
[Measures].[Custom]
} ON 1
FROM [Your Cube]
WHERE (
[Dimension].[HIERARCHY].Some_Member)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply