StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If I understand your requirement, you want rows for the month in the where clause or the month prior to the one in the where clause for which the total is over 100K. The where clause is going to affect the value that you see for the Total measure so you can't leave it in the where clause if you want to see the prior month's value, too. To see whether the row is for March or for February, you must include it in rows like this:</p> <pre><code>select ({ [Measures].[Total]}) on columns, non empty [Dim1].[h1].allmembers * [Dim1].[h2].allmembers * [Dim1].[h3].allmembers * [1 Date - Loss Date].[Date].[Year].allmembers * {[1 Date - Month End].[Month End Date].[Month].&[20120228],[1 Date - Month End].[Month End Date].[Month].&[20120331]} having [Measures].Total>100000 on rows from [Monthly Summary] </code></pre> <p>Are you getting the month from a parameter, and therefore looking for a generic way to get the previous month? If so, you can do something like this:</p> <pre><code>select ({ [Measures].[Total]}) on columns, non empty [Dim1].[h1].allmembers * [Dim1].[h2].allmembers * [Dim1].[h3].allmembers * [1 Date - Loss Date].[Date].[Year].allmembers * {[1 Date - Month End].[Month End Date].[Month].&[20120331].PrevMember,[1 Date - Month End].[Month End Date].[Month].&[20120331]} having [Measures].Total>100000 on rows from [Monthly Summary] </code></pre> <p>UPDATE: To get the current value when either the current month or the previous month is greater than 100,000 - regardless of the current month's total, you can do this:</p> <pre><code>select ({ [Measures].[Total]}) on columns, non empty [Dim1].[h1].allmembers * [Dim1].[h2].allmembers * [Dim1].[h3].allmembers * [1 Date - Loss Date].[Date].[Year].allmembers having [Measures].Total>100000 or ([1 Date - Month End].[Month End Date].[Month].&[20120331].PrevMember, [Measures].Total>100000) on rows from [Monthly Summary] where [1 Date - Month End].[Month End Date].[Month].&[20120331] </code></pre> <p>In the HAVING clause, the WHERE clause combines with the measure to filter out any rows not meeting the criteria for the specified month: March 2012. But it does not do this for expression after the OR because the tuple with PrevMember overrides the WHERE clause. </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