StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do I perform an "average days since event" type query in mdx?
text
Body
copied!<p>I have a cube that I have in the past used to report on counts of events. Let's say for the month of July I want to break down the number of events that have occurred for each product.</p> <p>I'd have something like this:</p> <pre><code>SELECT Measures.[Count] ON COLUMNS, Product.[Product Id].Members ON ROWS FROM [MyCube] WHERE [Time].[Month].[July 2012] </code></pre> <p>With an output like:</p> <pre><code> Count Car 5 Train 6 </code></pre> <p>Now I want to modify the output to get something like this:</p> <pre><code> Count Avg Days since sale Car 5 12 Train 6 14 </code></pre> <p>How do I do that?</p> <p>I've spent a few hours trying to find a solution to this in MDX, but can't find a way to do this. (I'm very new to MDX)</p> <p>I've found several solutions that would work if I included days in either COLUMNS or ROWS. For instance:</p> <pre><code>WITH MEMBER Measures.[NumDays] as count([Time].[Date].CurrentMember : [Time].[Date].&[2012-07-27T00:00:00]) SELECT ([Time].[Date].Members,{Measures.[Count],Measures.[NumDays]}) ON COLUMNS, [Product].[Product Id].Members ON ROWS FROM [MyCube] WHERE [Time].[Month].[July 2012] </code></pre> <p>This gets me really close. The result is something like:</p> <pre><code> July 1 July 2 ... Count Num Days Count Num Days ... Car 1 24 3 23 Train 0 24 1 23 </code></pre> <p>I could use this result set and get what I want in my .NET code. I can calculate the average days since sale by weighted average.<br> For car, for instance, it would be <code>(1*24 + 3*23)/(1 + 3)</code>. I'd have to do some mangled and unfortunate things in my .NET code to get this to work, and I'd also have to send back about 80 times as much data from the server as I need.</p> <p>The problem I've been running into with MDX is that as soon as I take away <code>[Time].[Date].Members</code> from the result set, <code>[Time].[Date].CurrentMember</code> is <code>[Time].[Date].All</code>, and I can't do any meaningful calculations from it.</p> <p>Is there a way to use the second MDX as a subquery, and rollup the values it returns in the way I need to?</p> <p>The subquery approach isn't necessary for my solution, but I am curious if that can be done. Any help would be appreciated. Thanks in advance.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload