SSAS MDX Query for date comparision data

Question

Post reply

SSAS MDX Query for date comparision data

swapp

SSC Enthusiast

Points: 164
More actions
December 13, 2012 at 7:21 am

#268098

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mackers SSCrazy Points: 2935 More actions · Answer 1

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

swapp SSC Enthusiast Points: 164 More actions · Answer 2

Thank you for your reply...

Here "Date" is dimention and count(distinct product) i calculated as measure.

---Swapnil

swapp SSC Enthusiast Points: 164 More actions · Answer 3

Hi Mack,

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

....Swapnil

swapp SSC Enthusiast Points: 164 More actions · Answer 4

Hi All,

Can anyone help me out in this.

I have below result,

C1C1 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