JYurkovich February 2016

Working with an OLAP Cube in Excel; determine monthly values within a larger date range?

I've been working in an OLAP cube as a pivottable in excel for a while now, but more recently I'm trying to integrate some calculated measures to streamline some things, and I've hit a wall.

The cube has date filters set up that specify the month & year (they get more specific, but finer levels aren't used).

The rows will list individual projects, and the values can reflect 2 measures: one that reflects the average score for the date range, and another that reflects the number of observations the average is based on.

I would like to create a calculated measure that will display the average score for each project as long as 2 criteria are met:

  1. There is a minimum of 100 observations for the whole date range
  2. there are no months with zero observations across the date range specified in the pivottable.

I should also clarify that the date ranges I use will vary and length, and will not always end with the most recent month, but they will always be in increments of whole months.

I'm part of the way there, as a calculated measure based on this will provide me with the average only if there are enough observations for the date range:

IIF([Measures].[OBSERVATIONS]>=100,[Measures].[AVERAGE],"^^")

Now, I need to add the criteria of no months with zero observations.

I have attempted to use COUNT(), but in this form it ignores the date range set in the pivottable and returns a count of all of the months there is any value for the project, including zeros.

COUNT(([Calendar].[Calendar].[Month],[Measures].[OBSERVATIONS]),EXCLUDEEMPTY)

I tried determining the lowest number of observations in a month using this expression, but again it ignores the date range, and does not reflect empty cells:

MIN([Calendar].[Calendar].[Month],[Measures].[OBSERVATIONS])

I think CurrentMember is what I need to in

Answers


SouravA February 2016

Using the EXISTING clause comes in handy at times when you want your calculation to recognize a certain selection(context). Following code is self explanatory. Let me know if it works.

WITH SET ZeroObservationMonths AS
FILTER
    (
    EXISTS
        (
        [Calendar].[Calendar].[Month].MEMBERS
        ,EXISTING [Calendar].[Calendar].[Date].MEMBERS
        )
     ,[Measures].[OBSERVATIONS] = 0
     )


MEMBER Measures.NewMeasure AS
IIF
 (
  [Measures].[OBSERVATIONS]>=100 AND COUNT(ZeroObservationMonths) > 0
  ,[Measures].[AVERAGE]
  ,"^^"
 )

SELECT [Project].[ProjectCode].MEMBERS ON 1,
Measures.[NewMeasure] ON 0
FROM [YourCube]
WHERE ({[Calendar].[Calendar].[Date].&D1: [Calendar].[Calendar].[Date].&D2})

EDIT: If you're planning on creating the measure inside excel, just have the below MDX code in the text box for "New calculated measure"

IIF
 (
  [Measures].[OBSERVATIONS]>=100 
  AND 
  COUNT
    (
     FILTER
        (
        EXISTS
            (
            [Calendar].[Calendar].[Month].MEMBERS
            ,EXISTING [Calendar].[Calendar].[Date].MEMBERS
            )
        ,[Measures].[OBSERVATIONS] = 0
        )
    ) > 0
  ,[Measures].[AVERAGE]
  ,"^^"
 )

EDIT 2: If the filtering can happen on any attribute, not just dates, replace EXISTING [Calendar].[Calendar].[Date].MEMBERS with EXISTING [Calendar].[Calendar].MEMBERS in the script above.

Post Status

Asked in February 2016
Viewed 2,478 times
Voted 9
Answered 1 times

Search




Leave an answer