[Mondrian] semi-additive measures with a shared time dimension
mcampbell at pentaho.com
Mon Jan 11 16:10:04 EST 2016
One thing I’d try is to run a query with a calculated member which gives you the unique name of the member returned by ClosingPeriod— e.g. Something like
With member measures.cp as ‘ClosingPeriod(Time.Month, Time.CurrentMember).UniqueName’
Select measures.cp on 0,
Time.year.members on 1
This should give you the unique name of the last Month present under each Year, regardless of whether the Year has a [December]. This might help identify why your formula is giving you unexpected results.
On 1/11/16, 11:52 AM, "mondrian-bounces at pentaho.org on behalf of Stefano Celati" <mondrian-bounces at pentaho.org on behalf of stefano.celati at bnova.it> wrote:
>I'm stuck on trying to solve this quiz and I hope someone could help me
>shade a light on it.
>I'm working on a sort of semi-additive measure that is an
>already-calculated YtD (at month level) value in the source table, so I
>need to expose this measure as a sum when the user asks for
>(year/)months details and as a sum of the last available month in the
>year, when they ask only for year level.
>I read the very good Diethard Steiner's article on his blog talking
>about this (in this case I'm using the CLOSINGPERIOD function), but I'm
>not getting what I would.
>I believe this is because my calendar is already filled with all the
>years/months ten years in advance and Mondrian is generating a query on
>that table that leads it to ask for figures of December on every year.
>Let me explain:
>Time Dimension: (pre-filled with years/months from 2010 to 2020)
>OTHER_IDS (to join other dimensions)
>SEMIADD_MEASURE (aggregation: sum)
>I built this NONADD_MEASURE with a CM and the formula looks like this:
>([Measures].[SEMIADD_MEASURE], ClosingPeriod ([Time].[Month],
>When I use this into Analyzer I see from log that Mondrian is looking up
>for Childrens of Years on the Time Dimension (remember it's always
>full-filled) then builds the final query against the fact table using a
>"WHERE MONTH=12" clause for every year, which makes some sense but
>obviously gives no results when the year is not ended yet.
>I don't know if this could help (because I haven't tried anything else
>at the moment), but my fact table also has its own Year and Month
>columns (the "external" time dim is because this is a multi-cube schema
>and I need to analyze data across this shared calendar).
>Thanks in advance for any kind of help
>Mondrian mailing list
>Mondrian at pentaho.org
More information about the Mondrian