October 13, 2014 at 3:33 pm
I am attempting to pull in the most recent visit date based on a cube containing claims data and am having trouble finding the solution.
I want my result set to look like this:
DimAttribute1 DimAttribute2 DimAttribute3 MostRecentVisit
x a f 4/14/2014
x b f 9/13/2014
y c g 6/30/2014
If I didn't want to bring in the MostRecentVisit but all Visits it might look like this:
DimAttribute1 DimAttribute2 DimAttribute3 VisitDate
x a f 4/14/2014
x a f 4/01/2014
x b f 1/1/2014
x b f 6/13/2014
x b f 9/13/2014
y c g 6/30/2014
Pulling in the second results set is easy:
select
NON EMPTY
{
Measure.SomeMeasure
} on COLUMNS
,
NON EMPTY
{
([DimAtt].[DimAttribute1].[DimAttribute1]
*[DimAtt].[DimAttribute2].[DimAttribute2]
*[DimAtt].[DimAttribute3].[DimAttribute3]
*[Date].[VisitDate].[VisitDate]
)
} ON ROWS
FROM [MyCube]
Any help is appreciated. Like I mentioned I have tried quite a few things using with no luck (TOPCOUNT, MAX functions tried but couldn't get to work right).
From a SQL perspective it would be something like this but I want to learn how to properly do this in the cube:
select DimAttribute1,DimAttribute2,DimAttribute3,max(VisitDate) as MostRecentVisitDate
from Table
Group by DimAttribute1,DimAttribute2,DimAttribute3
Thanks.
James
October 15, 2014 at 3:38 am
You can do this with a combination of TAIL and FILTER. Here is an example of getting the latest sale date by department in the AdventureWorks cube:
SELECT
{[Measures].[Reseller Sales Amount]} ON 0,
NON EMPTY
{
[Employee].[Department Name].[Department Name]
*
TAIL
(
FILTER
([Date].[Date].[Date],
NOT ISEMPTY([Measures].[Reseller Sales Amount])
)
, 1
)
}
ON 1
FROM
[Adventure Works]
What this query is doing is showing the latest date where the measure against it is not empty and cross joining that with the employee department. Just extrapolate this technique to your own code and it'll work as you intend.
🙂
October 15, 2014 at 5:26 pm
Thanks for the reply. I'm still no connecting the final dots to apply it my query. Could you write the code/psuedo code for what the mdx query wouldl look like based on my example? My issue is that I just keep pulling in the most recent date... not the most recent date based on the attributes being returned.
Thanks.
James
October 17, 2014 at 5:45 am
Okay, I think I misread your requirement before (sorry). Have a look at the following example, it shows the latest order date by Sales Channel and City in AdventureWorks. This should be easy enough to modify for your own needs:
WITH MEMBER MaxDate
AS
TAIL
(
FILTER([Date].[Date].[Date].MEMBERS,
[Measures].[Reseller Sales Amount])
, 1).Item(0).Name
SELECT
{[MaxDate],
[Measures].[Reseller Sales Amount]} ON 0,
NON EMPTY
{[Sales Channel].[Sales Channel].[Sales Channel]
*[Geography].[City].[City]}
ON 1
FROM
[Adventure Works]
October 20, 2014 at 9:52 am
That worked out perfect. Not sure why I was missing from before when it tried a similar solution but now I know the right way.
Thanks!
James
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply