MDX Calculating Month to Date Count
3 posters
Page 1 of 1
MDX Calculating Month to Date Count
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
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
Re: MDX Calculating Month to Date Count
Like the newbie I am, are we talking about Microsoft SQL/SSAS?
Lindell- Posts : 6
Join date : 2011-08-02
Re: MDX Calculating Month to Date Count
Add an indicator to your date dimension that tells you the current date.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: MDX Calculating Month to Date Count
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
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
Re: MDX Calculating Month to Date Count
Don't dynamically create. Update the table every day.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: MDX Calculating Month to Date Count
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
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
Re: MDX Calculating Month to Date Count
No hablo MDX.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Calculating Totals and Date Durations
» Fiscal Dim and Snapshot Dates for Budgets
» Month and Date Conformed Dimensions
» How can I write an MDX statement or query that selects the clients who paid last month but have not paid this(Current Month) month?
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Fiscal Dim and Snapshot Dates for Budgets
» Month and Date Conformed Dimensions
» How can I write an MDX statement or query that selects the clients who paid last month but have not paid this(Current Month) month?
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|