• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2135
  • Last Modified:

MDX Sum Filter

I have the following MDX query.  I am trying to select members with sum totals greater than $500 for a quarter.  I tried adding a filter without the sum and > than 500, that worked.  When I added the sum and greater than sign, I got the error: Query (26, 2) The > function expects a string or numeric expression for the 1 argument. A tuple set expression was used.

I am new to MDX and so I'm not sure what I need to change.  Please let me know.
--works fine
select {[Party Role].[Beneficiary].[Beneficiary Role].&[Host]} ON COLUMNS,
{Filter ([Year Quarter Desc].Members, [Measures].[Total Spend Amount]> 500)} ON ROWS
FROM Spends
 
--doing a sum and greater than does not work
select {[Party Role].[Beneficiary].[Beneficiary Role].&[Host]} ON COLUMNS,
{Filter ([Year Quarter Desc].Members, sum([Measures].[Total Spend Amount])) > 500} ON ROWS
FROM Spends

Open in new window

0
Kaporch
Asked:
Kaporch
1 Solution
 
KaporchAuthor Commented:
I think I might have found the answer on my own, below.
WITH MEMBER Measures.Year AS SUM
	([Year Quarter Desc].&[2008/Q1], [Measures].[Total Spend Amount])
	select [Activity Start Date].[Date] ON COLUMNS,
	{Filter ([Year Quarter Desc].&[2008/Q1], [Measures].[Year] > 500), 
	Filter([Year Quarter Desc].&[2008/Q2], [Measures].[Year] > 500)} ON ROWS
	from spends
WHERE {[Party Role].[Beneficiary].[Beneficiary Role].&[Host]}

Open in new window

0
 
neasarCommented:
Thanks :) very handy indeed.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now