CODEWITHSUNDEEP
ssasmdx
Gabriel Sadaka
Gabriel Sadaka

Reputation: 1746

Equivalent to max group by in mdx

How do I get the sales for the last product of a cross join of each product group and brand? I had a look at the Tail function but I can't get it to work properly.

This is the MDX I have so far: 

SELECT {[Measures].[Sales Amount]} ON COLUMNS,
{
    [Dim Brands].[Brand Name].[Brand Name].ALLMEMBERS *
    [Dim Product Groups].[Product Group Name].[Product Group Name].ALLMEMBERS *
    Tail ([Dim Products].[Product Name].[Product Name].ALLMEMBERS)
}

It only returns the last product for the whole cube rather than the last product for each brand and product group.

Upvotes: 0

Views: 882

Answers (2)

whytheq
whytheq

Reputation: 35557

Separating out the sets should speed this script up. Like the following:

WITH 
  SET [allBrands] AS 
    [Dim Brands].[Brand Name].[Brand Name].ALLMEMBERS 
  SET [allGroups] AS 
    [Dim Product Groups].[Product Group Name].[Product Group Name].ALLMEMBERS 
  SET [A] AS 
    Generate
    (
      {[allBrands] * [allGroups]} AS s
     ,
        s.Current
      * 
        Tail
        (
          NonEmpty
          (
            [Dim Products].[Product Name].[Product Name].ALLMEMBERS
           ,[Measures].[Sales Amount]
          )
         ,1
        )
    ) 
SELECT 
  NON EMPTY 
    {[Measures].[Sales Amount]} ON 0
 ,NON EMPTY 
    [A] ON 1
FROM [YourCube];

Against Microsoft's AdvWrks a similar, and maybe more useful, variant of the above would be to use TopCount rather than the slightly artitrary Tail:

WITH 
  SET [allYears] AS 
    [Date].[Calendar].[Calendar Year].MEMBERS 
  SET [allCountries] AS 
    [Customer].[Customer Geography].[Country].MEMBERS 
  SET [A] AS 
    Generate
    (
    {[allYears] * [allCountries]} AS s
     ,
        s.Current
      * 
        TopCount
        (
          [Product].[Product Categories].[Product].ALLMEMBERS
         ,2
         ,[Measures].[Internet Sales Amount]
        )
    ) 
SELECT 
  {[Measures].[Internet Sales Amount]} ON 0
 ,[A] ON 1
FROM [Adventure Works];

This results in the following:

enter image description here

Upvotes: 2

SouravA
SouravA

Reputation: 5243

You can use the GENERATE function to generate the product for every combo or Brand and product group. The EXISTING takes care of the scope.

WITH SET LastProductForEachBrandAndProductGroup AS

    GENERATE

    (
       EXISTING
        NonEmpty
        (
            [Dim Brands].[Brand Name].[Brand Name].ALLMEMBERS, [Dim Product Groups].[Product Group Name].[Product Group Name].ALLMEMBERS
        )

    ,

    Tail (
            [Dim Products].[Product Name].[Product Name].ALLMEMBERS
         )

    )


SELECT {[Measures].[Sales Amount]} ON COLUMNS,
NonEmpty({
    [Dim Brands].[Brand Name].[Brand Name].ALLMEMBERS *
    [Dim Product Groups].[Product Group Name].[Product Group Name].ALLMEMBERS *
    LastProductForEachBrandAndProductGroup
}) ON ROWS
FROM YourCube

If by last, you imply the last member in any sorted(by some measure) list, the above would need some more work. Let me know how it works out for you.

Upvotes: 2

Related Questions