CODEWITHSUNDEEP
mdxaverage
JM Hicks
JM Hicks

Reputation: 1292

MDX Add Calculated Member For Average

I have a working query I would like to change a little bit.

The working query gives me a calculated measure value for each campaign that a user has participated in.

Since in this query example, there are 2 calculated values 0, and 7 each for the 2 campaigns this user participated in, I should be able to convert the query to display a single user row with the average of the two values, e.g. 3.5 .

That's where I'm stuck. Does anyone know how to add-replace the [Day Diff Exposure to Download] in the results to an [Avg Day Diff...] calculation?

Thank you very much for your help.

Here is the original working query:

with
member [measures].FirstEventDate as CDate(Head(NonEmpty([DIM DATE].[Date].[Date], ([Measures].[Count], [DIM USER].[Email].CurrentMember, [DIM CAMPAIGN].[Campaign].CurrentMember))).item(0).member_caption)
member [measures].[Day Diff Exposure to Download] as ([DIM EVENT].[Event].[Event Name].&[download], [measures].FirstEventDate) - ([DIM EVENT].[Event].[Event Name].&[exposure], [measures].FirstEventDate)
select
[measures].[Day Diff Exposure to Download] on columns,
NonEmpty (
    {([DIM USER].[Email].[User Email].&[[email protected]],
    [DIM CAMPAIGN].[CampaignTypeName].[Campaign Name])},
    [DIM EVENT].[Event].[Event Name].&[download]
) on rows
from [MyCube]

Here is my currently unsuccessful attempt to upgrade the query:

with
member [measures].FirstEventDate as CDate(Head(NonEmpty([DIM DATE].[Date].[Date], ([Measures].[Count], [DIM USER].[Email].CurrentMember, [DIM CAMPAIGN].[Campaign].CurrentMember))).item(0).member_caption)
member [measures].[Day Diff Exposure to Download] as ([DIM EVENT].[Event].[Event].&[download], [measures].FirstEventDate) - ([DIM EVENT].[Event].[Event].&[exposure], [measures].FirstEventDate)
member [measures].[Avg Day Diff] as
Avg(
    NonEmpty(
        {[DIM CAMPAIGN].[Campaign].[Campaign]},
        (
            [DIM USER].[Email].CurrentMember,
            [DIM EVENT].[Event].[Event].&[download]
        )
    ),
    [measures].[Day Diff Exposure to Download]
)
select
[measures].[measures].[Avg Day Diff] on columns,
NonEmpty (
    {[DIM USER].[Email].[Email].&[[email protected]]},
    [DIM EVENT].[Event].[Event].&[download]
) on rows
from [MyCube]

Unfortunately for now, the attempted query doesn't return the value 3.5, but returns -13738.3333333 instead.

Upvotes: 1

Views: 1355

Answers (1)

Marc Polizzi
Marc Polizzi

Reputation: 9375

You might need to specify the measures as well within the nonEmpty set; something like:

NonEmpty( [measures].FirstEventDate * { ... } )

Upvotes: 1

Related Questions