TM1 Forum

Hi everybody,
I must define a MDX query based on the schema depicted below:

<Schema name="HDGL_SCHEMA">
<Dimension name="GROUP">
<Hierarchy hasAll="true" allMemberName="All Coils" primaryKey="key_coils">
<Table name="dim_coils"/>
<Level name="Campaign" column="labelCampaign" uniqueMembers="true" type="String"/>
<Level name="Coil" column="coilid_coil" uniqueMembers="false" type="Integer"/>
<Level name="Sector" column="idSector" uniqueMembers="false" type="Integer"/>
</Hierarchy>
</Dimension>

<Cube name="HDGL">
<Table name="fact_table"/
<DimensionUsage name="COIL" source="COIL" foreignKey="key_coils"/>
<Measure name="KPI1" column="KPI1" aggregator="sum" formatString="###0.00000"/>
</Cube>
</Schema>

So, I have a measure KPI1 and a hierarchy named GROUP (Sector->Coil->Campaign) and the aggregation functions for the KPI1, according to each level, are:
KPI for sector = KPI1
KPI for the coil = sum(KPI1)
KPI for the campaign = avg(sum(KPI1))

I would to create a query that select the the calculated KPI for the hierarchy level "Campaign" which should be based on the expression:
sum(KPI)/Count( number of coil related to each Campaign).

How I can make this? I have tried with the following query... but without results...

with member [Measures].[KPI1 - CAMPAGNA] as '(Sum({[Measures].[KPI1]}) / Count([Coil].[All Coils]))'
select {[Measures].[KPI1 - CAMPAGNA]} ON COLUMNS, NON EMPTY
Hierarchize({[COIL].[All Coils].Children}) ON ROWS
from [HDGL]

best regards
nico

Hi Nico,
check this example below with the food mart db in mondrian.

You would adjust it to your schema.

best regards,

Thanks Marcus!
I'll try your suggestion (also for my other thread) next week.

Thanks again!
regards

nico