March 14, 2012 at 7:05 am
Hi,
I'm new to MDX.
I want to count how many 'high sales' we have per customer. High sales means we have a sales of more then 100. So we want to count for a given period how many days a customer has had a sales amount of more than 100.
When we take it from Adventure works, then I would be something like this:
WITH
SET High AS
{FILTER([Customer].[Customer].members*[Date].[Date].[Date], [Measures].[Internet Sales Amount].value>=100)}
MEMBER Measures.CountHighSales AS
COUNT(High)
SELECT
{[Customer].[Customer].members} ON ROWS,
{[Measures].[Internet Sales Amount], Measures.[CountHighSales]
} ON COLUMNS
FROM [Adventure Works]
WHERE ([Date].[Date].[Date].&[20080401]:[Date].[Date].[Date].&[20080430])
But this gives for each customer the same result.
How can I reach the expected result?
Thanks for any help.
July 24, 2012 at 5:40 am
Hi there,
I am facing the same issue, when I use hierarchy members to filter against any measure and pull the same hierarchy members on axis, it give the same result for each hierarchy member.
--here is my MDX --
WITH
SET x1 AS
Filter
(
[Project].[-Operating Group-].[Project].MEMBERS
,
[Measures].[ITD Variance GP %] > 0.05
)
MEMBER x2 AS
Count(x1)
SELECT
x2 ON 0
,NON EMPTY
{
[GL Date].[Fiscal].[Year].&[2012]
*
[Project].[-Operating Group-].[Operating Group].MEMBERS
} ON 1
FROM ABC;
August 3, 2012 at 6:01 am
I'm no guru...but check this out, based on Adventure works
here is raw data for sales for every combo of customer and month
CalendarHierachy is my own user defined 3 level hiearchy of year ,month, day
WITH SET MyCustomerDateComboSet AS '[Dim Customer].[Customer Key].Children * [Order Date].CalendarHierarchy].[Month].members'
SELECT [Measures].[Sales Amount] ON 0,
NONEMPTY(MyCustomerDateComboSet) ON 1
FROM [MyFirstCube]
you get something like this , drill down for each cust id and month and the sales amount
Customer Month Sales
11000 Jul 2005 3399
11000 Aug 2005 2700
11000 Oct 2005 2499
11001 Jan 2005 2600
11001 Feb 2005 2499
11001 Mar 2005 2610
11002 JAn 2005 1500
From what i gather you want is a summary for each distinct customer a count of the months
say where the sales value exceeds say 2500
so for above we want a summary as this
Customer Count
11000 2
11001 2
11002 0
here is the code :
WITH SET MyCustomerSet as '[Dim Customer].[Customer Key].Children'
MEMBER MemberRank as 'Rank([Dim Customer].[Customer Key].CurrentMember, MyCustomerSet)'
MEMBER CountMonthlySalesThan2500 as COUNT(FILTER(MyCustomerSet.Item(MemberRank-1) * [Order Date].[CalendarHierarchy].[Month].members, [Measures].[Sales Amount] > 2500))
SELECT CountMonthlySalesThan2500 on 0,
MyCustomerSet On 1
FROM [MyFirstCube]
walk through :
MyCustomerSet - all Customers
MemberRank - Rank of each Customer in the set
CountMonthlySalesThan2500 - Take each member in the customer set using rank and get all combo of dates and then filter out for sales > 2500 and then finally count them...
I'm not saying this is the defacto solution but it sort of works...have a play to suit your needs ...its a steer anyway if nowt else
Cheers
Robin Rai
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply