CODEWITHSUNDEEP
sqlmdx
lbarreira
lbarreira

Reputation: 170

How to get data from last days

I'm a newcomer to SQL MDX and don't know exactly how to achieve this.

I need to get data from my cube for the last X days from the last available data.

The following is my code:

SELECT { [Measures].[Fact Stays Count], [Measures].[Time Spent] } ON COLUMNS,
       NON EMPTY { ( [Dim Locals].[Local Description].[Local Description].ALLMEMBERS * [FK Date].[Date].[Date] ) } ON ROWS
FROM
(
select { TAIL(FILTER([FK Date].[Date].MEMBERS, NOT ISEMPTY([FK Date].[Date].CURRENTMEMBER)),30) } ON COLUMNS
FROM (
    SELECT ( STRTOSET(@userId, CONSTRAINED) ) ON COLUMNS
    FROM [DW]
)
)

The problem is the query returns the last 30 days where data exists, not the last 30 consecutive calendar days.

How can I change the query to get the results I want?

Upvotes: 0

Views: 1127

Answers (1)

mmarie
mmarie

Reputation: 5638

Try this. The only thing I changed is the select with the dates in it. Instead of asking for the last 30 days where there is data for the measures, I'm asking for the last day where there is data for the measures, getting that last item and then doing the lag of 29 days for the beginning of the date range and then without the lag (to the last day with data) for the end of the date range.

SELECT { [Measures].[Fact Stays Count], [Measures].[Time Spent] } ON COLUMNS,
       NON EMPTY { ( [Dim Locals].[Local Description].[Local Description].ALLMEMBERS * [FK Date].[Date].[Date] ) } ON ROWS
FROM
(
select { TAIL(FILTER([FK Date].[Date].MEMBERS, NOT ISEMPTY([FK Date].[Date].CURRENTMEMBER)),1).item(0).lag(29): TAIL(FILTER([FK Date].[Date].MEMBERS, NOT ISEMPTY([FK Date].[Date].CURRENTMEMBER)),1).itm(0)} ON COLUMNS
FROM (
    SELECT ( STRTOSET(@userId, CONSTRAINED) ) ON COLUMNS
    FROM [DW]
)
)

Be aware that the way you have the query now will return the last day where there is data for both measures. If those two measures don't line up it might not provide what you want. For instance, if there is data through Dec 30 2013 on Fact Stays Count and data through Jan 5 2014 on Time Spent, it would return Dec 30 2013. If you want it to depend on both measures, you are good. If you want it to depend on one measures, you can switch it to be something like the below instead.

Tail(Filter([FK Date].[Date].[Date].MEMBERS.MEMBERS, [Measures].[Fact Stays Count] >0),1).item(0)

Upvotes: 1

Related Questions