SSAS MDX Query for date comparision data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » SSAS MDX Query for date comparision data

SSAS MDX Query for date comparision data

Rate Topic

Display Mode

Topic Options

Author

Message

swapp

Posted Thursday, December 13, 2012 7:21 AM

Forum 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

Mackers

Posted Thursday, December 13, 2012 7:59 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 117, Visits: 460

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

swapp

Posted Thursday, December 13, 2012 8:19 AM

Forum 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

swapp

Posted Friday, December 14, 2012 12:30 AM

Forum 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

swapp

Posted Thursday, January 24, 2013 12:57 AM

Forum 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 »

Permissions