MDX Calculating Month to Date Count

View previous topic View next topic Go down

MDX Calculating Month to Date Count

Post  cjrinpdx on Thu Jan 26, 2012 2:43 pm

I have a measure group [Measures].[Event Count] and a dimension [Event Date].[Calendar].[Date].

Events can be scheduled in the future, so I want to create a month to date count that will only count events where the event date is < Now().

The users usually look at the count by month, so if they select January they want to only get events where the event date is < Now().

Thanks



cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: MDX Calculating Month to Date Count

Post  Lindell on Fri Jan 27, 2012 4:46 am

Like the newbie I am, are we talking about Microsoft SQL/SSAS?

Lindell

Posts : 6
Join date : 2011-08-02

View user profile

Back to top Go down

Re: MDX Calculating Month to Date Count

Post  BoxesAndLines on Fri Jan 27, 2012 10:11 am

Add an indicator to your date dimension that tells you the current date.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: MDX Calculating Month to Date Count

Post  cjrinpdx on Fri Jan 27, 2012 7:13 pm

Yes, thank you, I was thinking of that. What would that MDX look like?

This MDX works when I hardcode the date, but when I dynamically try to create it (commented out line) it doesn’t return the correct value.

Select
{([Measures].[Event Count])} On Columns,
{
PeriodsToDate(
[Event Date].[Calendar].[Month],
[Event Date].[Calendar].[Date].&[20120125]
--[Event Date].[Calendar].[Date].&[Format(Now()-1,"yyyyMMdd")]
)
} On Rows
From Bidw_Cube

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: MDX Calculating Month to Date Count

Post  BoxesAndLines on Mon Jan 30, 2012 12:16 am

Don't dynamically create. Update the table every day.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: MDX Calculating Month to Date Count

Post  cjrinpdx on Mon Jan 30, 2012 1:39 pm

I understand the concept, I was just trying to figure out the MDX. So something like this should work? Thanks!

Select
{([Measures].[Event Count])} On Columns,
{
PeriodsToDate(
[Event Date].[Calendar].[Month],
[Event Date].[Calendar].[Date].&[IsCurrentDate] = 1
)
} On Rows
From Bidw_Cube

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: MDX Calculating Month to Date Count

Post  BoxesAndLines on Mon Jan 30, 2012 2:16 pm

No hablo MDX.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: MDX Calculating Month to Date Count

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum