exclude field in aggragation

Not related to a specific OLAP tool. (Includes forum policies and rules).
Post Reply
nko83
Posts: 5
Joined: Mon May 16, 2011 7:31 am
OLAP Product: mondrian
Version: 1.2
Excel Version: 2010

exclude field in aggragation

Post by nko83 »

i everybody.

I have a problem to execute a MDX query.
I have a hierarchy Sector->Coil->Campaign and a mesure KPI4 for each Sector.

A coil is composed by 20 Sector, so I would to aggregate 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
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: exclude field in aggragation

Post by Marcus Scherer »

this one should do it, once again based on the mondrian example.
Assuming you want to always omit the first and last sector.

Code: Select all


with member [Measures].[coil_rest] as 'Sum(
EXCEPT(Descendants([Store].[Store State].CurrentMember,[store].[store city]), 
{[Store].[Store city].CurrentMember.FirstChild, [Store].[Store city].CurrentMember.LastChild}), 
[Measures].[Unit Sales])'


select {[Measures].[Unit Sales], [Measures].[coil_rest]} ON COLUMNS,
  {[Store].[Store State].Members} ON ROWS
from [Sales]
where [Time].[1997]
Kimberybery
Posts: 1
Joined: Tue Apr 17, 2018 1:54 pm
OLAP Product: PAW
Version: TM1
Excel Version: Excel 2007

Re: exclude field in aggragation

Post by Kimberybery »

For more details on MDX query basics and MDX-SQL comparison, see the MDX for Everyone guide, or the official MDX documentation by MSDN.
Post Reply