August 10, 2010 at 5:05 pm
Hi, I need some help to make a mdx query. I will explain my problem:
Sales between 01-01-2009 and 31-01-2009:
Bike Name | Sales
A | 2
B | 1
C | 4
F | 7
Sales between 01-01-2010 and 31-01-2010
Bike Name | Sales
A | 2
B | 1
G | 3
k | 20
So I want the following result:
Bike Name | Sales
C | 4
F | 7
I want to get all bikes (from 01-01-2009 and 31-01-2009) that haven't any sales between: 01-01-2010 and 31-01-2010
To solve this pb I made 2 mdx query! one for first interval and another for the second, and then, by code (vb.net) I obtained the differences... but this is a very slow and inefficient solution.
Can anyone please help! I really need this!
Thanks in advance
Cafc
August 10, 2010 at 6:17 pm
It's a little ugly and I'd definitely recommend looking at using a subcube context (I didn't have time) instead, but you could use something like...
SELECT
FILTER(FILTER([Products].[Product_Name].MEMBERS, [Measures].[Sales] = 0), ([Measures].[Sales], [Time_Dim].[Range_Other] ) <> 0) ON ROWS,
[Measures].[Sales] ON COLUMNS
FROM
[Cube_Name]
WHERE
[Time_Dim].[Range_First]
Steve.
August 11, 2010 at 2:38 am
I think a query like the one below also returns the desired result. Although I'm not sure if this is the most efficient way to do it.
with
set set1 as
filter(
[Product].[Product].[Product].members,
(
[Product].[Product].CurrentMember,
[Date].[Calendar].[Month].&[2001]&[7]
) > 0
) -- products sold in period 1
set set2 as
filter(
[Product].[Product].[Product].members,
(
[Product].[Product].CurrentMember,
[Date].[Calendar].[Month].&[2002]&[7]
) > 0
) -- products sold in period 2
select
{[Measures].[Order Count]} on 0,
except([set1], [set2]) on 1 -- all products that sold in period1 but not in period2
from [Adventure Works]
where [Product].[Category].&[1] -- bikes
August 11, 2010 at 12:34 pm
Hi,
thank you very much for both your replies!
I haven't the opportunity to test it but i will tomorrow and post here the results! thank you again
Cafc
August 12, 2010 at 12:01 pm
Hi, Steve
Thank you very much for your reply. Indeed, all my Measure.Sales has values >0... so the result is not what I would expected!
But, thanks any way.
Hi Dirk Wegener, adapted your code to my situation:
with
set set1 as
filter(
[Dim Costumer].[Cost Cod].Allmembers,
(
[Dim Costumer].[Cli Cod],
[Dim Date].[Calendar].[year].&[2006]
) > 0
) -- products sold in period 1
set set2 as
filter(
[Dim Costumer].[Cli Cod].allmembers,
(
[Dim Costumer].[Cli Cod],
[Dim Date].[Calendar].[year].&[2007]
) > 0
) -- products sold in period 2
select
{[Measures].[Qty Cost]} on 0,
except([set1], [set2]) on 1 -- all products that sold in period1 but not in period2
from [myCube]
where [Dim Costumer].[Cost Cod] -- bikesFROM [LojasDM]
But, it gives an error: "The Cost Cod hierarchy already appears in the Axis1 axis"
Thanks
Cafc
August 12, 2010 at 1:49 pm
It was an example, not going to do all of the work for you 🙂 (ie if you play with those operatorsyou'll find the combination that does what you're looking for, but anyways)
Looks like you have repeated/used the wrong dimension in the first set (should be Cli Cod not Cost Cod).. Possibly something like below will work?
with
set set1 as
filter(
[Dim Costumer].[Cli Cod].Allmembers,
(
[Dim Costumer].[Cli Cod],
[Dim Date].[Calendar].[year].&[2006]
) > 0
) -- products sold in period 1
set set2 as
filter(
[Dim Costumer].[Cli Cod].allmembers,
(
[Dim Costumer].[Cli Cod],
[Dim Date].[Calendar].[year].&[2007]
) > 0
) -- products sold in period 2
select
{[Measures].[Qty Cost]} on 0,
except([set1], [set2]) on 1 -- all products that sold in period1 but not in period2
from [myCube]
where [Dim Costumer].[Cost Cod] -- bikesFROM [LojasDM]
Steve.
August 13, 2010 at 11:53 am
Hi Steve!
Thank you very much for your help!
Cafc
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply