Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSAS MDX Query for date comparision data Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 6:40 AM
Points: 8, Visits: 17
Hi friends,

Can anyone help me out for writing MDX for finding products which are present for current date but not in previous date. Below is SQL query for the same.

select count(distinct Product) from table_A
where Date='2012-12-12'
and Product not in (select distinct Product from table_A
where Date='2012-12-01')

thanks in advance...
----Swapnil
Post #1396181
Posted Thursday, December 13, 2012 7:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
Try something along the lines of

with
set [Product List] As

NONEMPTY(
{[Product].[Product Level 4].[All].Children}
, ([Measures].[Revenue],[Date].[Day].&[20121212])
)
-
NONEMPTY(
{[Product].[Product Level 4].[All].Children}
, ([Measures].[Revenue],[Date].[Day].&[20121201])
)

select [Product List] on rows,
{[Date].[Day].&[20121201],[Date].[Day].&[20121212]} on columns
from [Your Cube]
where ([Measures].[Revenue])

Mack
Post #1396201
Posted Thursday, December 13, 2012 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 6:40 AM
Points: 8, Visits: 17
Thank you for your reply...
Here "Date" is dimention and count(distinct product) i calculated as measure.
---Swapnil
Post #1396218
Posted Friday, December 14, 2012 12:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 6:40 AM
Points: 8, Visits: 17
Hi Mack,

thank you for your help. Working fine now...

....Swapnil
Post #1396506
Posted Thursday, January 24, 2013 12:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 6:40 AM
Points: 8, Visits: 17
Hi All,

Can anyone help me out in this.
I have below result,

C1 C1 C1
26-Nov-12 3-Dec-12 10-Dec-12
Count Of Product 196 162 210
% L Count 45.18% 40.49% 49.49%
% C Count 54.82% 59.51% 79.51%
% New Items 0.00% 0.00% 0.00%

Here in % New items i want to write MDX to show the product present for 10-Dec-12 but not in 3-Dec-12,03-Dec-12 but not in 26-Nov-12 according to this product present for 26-Nov-12 are 100% bcoz no previous date is selected.

thanks
Swapp
Post #1410950
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse