Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

MDX Calculating Month to Date Count

Go down

MDX Calculating Month to Date Count Empty MDX Calculating Month to Date Count

Post  cjrinpdx 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

Back to top Go down

MDX Calculating Month to Date Count Empty Re: MDX Calculating Month to Date Count

Post  Lindell 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

Back to top Go down

MDX Calculating Month to Date Count Empty Re: MDX Calculating Month to Date Count

Post  BoxesAndLines Fri Jan 27, 2012 10:11 am

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

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

Back to top Go down

MDX Calculating Month to Date Count Empty Re: MDX Calculating Month to Date Count

Post  cjrinpdx 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

Back to top Go down

MDX Calculating Month to Date Count Empty Re: MDX Calculating Month to Date Count

Post  BoxesAndLines Mon Jan 30, 2012 12:16 am

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

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

Back to top Go down

MDX Calculating Month to Date Count Empty Re: MDX Calculating Month to Date Count

Post  cjrinpdx 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

Back to top Go down

MDX Calculating Month to Date Count Empty Re: MDX Calculating Month to Date Count

Post  BoxesAndLines Mon Jan 30, 2012 2:16 pm

No hablo MDX.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

MDX Calculating Month to Date Count Empty Re: MDX Calculating Month to Date Count

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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