In MDX what is the ParallelPeriod of UnknownMember

mdxssas

Lets say I have a time dimension with the levels all, year, quarter, month, day. I have some sales per month and a calculated measere [sales last year]

WITH MEMBER [Measures].[sales last year] AS (ParallelPeriod([Time].[Time].[Year],1,[Time].[Time].CurrentMember), [Measures].[sales])
SELECT {[Measures].[sales], [Measures].[sales last year]} ON 0
, [Time].[Time].[Month].Members ON 1
FROM [Sales]

Now the time dimension has a visible UnknownMember and it shows as [sales last year] the same value I see for january of my last year in the hierarchy.

Why is that so?
How can I avoid it? I mean the sum of all [sales last year] should be the same as the sum of all [sales] over all years except the first.

Best Answer

The way `PARALLELPERIOD' works is so that it moves up the hierarchy up to the specified level and then moves back or forward the number of periods, then moves down again using the same path.

In this 'moving down the path' parallelperiod will descend the same member ordinal again. So if it moved up from the first member (in your example january) it will move down the first member again. If it moved up the second member (in your example february) it will move down the second member again.

This of course breaks with an unknown hierarchy, since each child is the first member of it's parent.

So lets say for example you have this hierarchy

     2015             unknown
jan   feb    mar      unknown

And then you do a parallelperiod on the leaf member of the unknown hierarchy, the parallelperiod will move up to the year level of the unknown hierarchy, move back a year and move down to the leaf level again, remembering it moved up from the first member of the unknown hierarchy and moving down the first member of the 2015 hierarchy, which is jan 2015.

Related Solutions

SSAS / MDX Query – Add an extra dimension when using Order

I'm not quite sure what you're asking, but I think you want to do the following (correct me if I'm wrong):

You want to see a breakdown of sales by month, one row for each month, with a total for all months at the bottom.
You want to see the items across the columns, ordered by the item with the largest sales volume to the smallest.

select non empty
       order ([Item].[Item].Children
             ,([Measures].[Total Line Value]
              ,[Date].[Month].[All])
             ,BDESC)
    on Columns
       non empty
      ,[Date].[Month].Children
    on Rows
  from [Sales Analysis]
 where [Measures].[Total Line Value]

The columns are each individual item, sorted by the total Line Value over all months, i.e. from all-time best seller to all-time lowest seller, with items having no sales at all excluded.

The rows are the months for all time where data exists. You can use a set definition to specify any date range you want. Note that this assumes the existence of a dimension called [Date].

This query will show the items in order of total sales across the columns and the sales for each month by item down the rows. I'm not 100% sure if this is what you want; feel free to clairfy your question if I'm not on the right track.

ParallelPeriod returning null for Feb. 29 in date dimension

Another option would be to use a union to get parallel periods for the current and previous member. Then extract the first item out of this, which will always be the current member (except in the case of Feb 29 where it should fall back to the previous member).

That is, effectively coalesce with the previous member parallel period as per below:

union 
(
    PARALLELPERIOD(
        [Date].[Fiscal Detail].[Fiscal Year],1,
        [Date].[Fiscal Detail].currentmember) 
    *(will be null if Feb 29)*
    ,
    PARALLELPERIOD(
        [Date].[Fiscal Detail].[Fiscal Year],1,
        [Date].[Fiscal Detail].currentmember.lag(1))  
    *(will be the prior year Feb 28 when above is Feb 29 )*    
).item(0)  *(get the first member out of the union set)*

Best Answer

Related Solutions

SSAS / MDX Query – Add an extra dimension when using Order

ParallelPeriod returning null for Feb. 29 in date dimension

Related Question