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
Help on MDX query!!
-
- 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: Help on MDX query!!
Hi Nico,
check this example below with the food mart db in mondrian.
You would adjust it to your schema.
best regards,
check this example below with the food mart db in mondrian.
You would adjust it to your schema.
best regards,
Code: Select all
with member Measures.camp_avg as 'AVG(
Descendants([store].[Store state].currentmember, [store].[store name]),
[Measures].[Unit Sales]
)'
select {[Measures].[Unit Sales], Measures.camp_avg} ON COLUMNS,
{[Store].[store state].members} ON ROWS
from [Sales]
where [Time].[1997]
-
- Posts: 5
- Joined: Mon May 16, 2011 7:31 am
- OLAP Product: mondrian
- Version: 1.2
- Excel Version: 2010
Re: Help on MDX query!!
Thanks Marcus!
I'll try your suggestion (also for my other thread) next week.
Thanks again!
regards
nico
I'll try your suggestion (also for my other thread) next week.
Thanks again!
regards
nico