November 18, 2004 at 2:51 pm
I am new to MDX.
I am trying to set up a cube to analyse population data.
The fact table has one row per cohort of people per period. The measure (CohortPersonCount) is a count of people in the cohort. Importantly the count measure is repeated over the period dimension.
Fact
----
CohortID, PeriodID, CohortPersonCount, Dimenison3, Dimension4..
When I explore the cube the measure I am interested in producing is a the sum of CohortPersonCount - but only for distinct CohortID.
I am able to produce the distinct count of Cohort in MDX but the sum seems to always include duplicates across multiple periods.
Member [Measures].[DistinctCohortCountWorksOK] as '
count
(
nonemptyCrossjoin({Descendants([Cohort].CurrentMember, [Cohort].[CohortId])}, {[Measures].[CohortPersonCount]})
)
'
...but this gets duplicates...
Member [Measures].[PersonCountDoesNotWork] as '
sum
(
Distinct(nonemptyCrossjoin({Descendants([Cohort].CurrentMember, [Cohort].[CohortId])}, {[Measures].[CohortPersonCount]})) as S1,
S1.Current.Item(1)
)
'
How can I create a measure of the number of people which adds properly?
November 18, 2004 at 8:41 pm
Is the aim to sum only the latest (most recent) cohortcount for each cohortid?
When you say it includes duplicates across multiple periods, do you want the sum to be time related or time independant?
Steve.
November 19, 2004 at 5:26 am
Hi Steve,
The cohortCount will always be the same in every row of the fact table with the same cohortID, so we could either pick the lastest or use min or max.
Ideally I would like to set up a measure which would work in any situation (i.e. time related or time independant).
Thanks, Renato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply