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
exclude field in aggragation
-
- 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
this one should do it, once again based on the mondrian example.
Assuming you want to always omit the first and last sector.
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]
-
- Posts: 1
- Joined: Tue Apr 17, 2018 1:54 pm
- OLAP Product: PAW
- Version: TM1
- Excel Version: Excel 2007
Re: exclude field in aggragation
For more details on MDX query basics and MDX-SQL comparison, see the MDX for Everyone guide, or the official MDX documentation by MSDN.