none
Question to display current date on columns for top 5 sales for current year to date.

    Question

  • Hi All,

    I have the following MDX query to show top 5 sales by employee from current to date 

    WITH 
              MEMBER [Date].[Date].[X] as 'AGGREGATE(PeriodsToDate([Date].[Date].[Year],[Date].[Date].[All].[2011].[Q2].[April].[02 Apr 2011]))'
       
    SELECT
     ([Measures].[Sales Amt]) on 0, 
     
     topcount((Filter({[Employee].[Employee].[Employee].Members}
       , [Measures].[Sales Amt] <> 0 )),5,([Measures].[Sales Amt] ))  on 1
      
    FROM
     [Sales] 
     
    WHERE  ([Date].[Date].[X])

    the above query will give us the result set

    EmployeeName

                         SalesAmt

    EmployeeA

    $90

    EmployeeA

    $80

    EmployeeA

    $70

    EmployeeA

    $60

    EmployeeA

    $50

    My Goal is to show(DISPLAY) the current date on the columns without changing the output .the output should look as below

    EmployeeName

                       

                       SalesAmt

                      16-May-12  

                                

    EmployeeA

    $90

    EmployeeA

    $80

    EmployeeA

    $70

    EmployeeA

    $60

    EmployeeA

    $50

    Thanks For your help in advance.Please let me know if i need to provide more information about my question.

    thanks,

    Krishna


    krishna

    Monday, June 18, 2012 4:56 AM

Answers

  • This should get the result you are after. But it depends on what client tool you are using. If you are getting a flattened resultset the column will have the measure name and date concatenated. If you are getting back a cellset you should get both values as column headers.

    WITH 
              MEMBER [Date].[Date].[Sales Amt YTD] as SUM(PeriodsToDate([Date].[Date].[Year],[Date].[Date].CurrentMember),
    [Measures].[Sales Amt] 
    ) SELECT {[Measures].[Sales Amt YTD]} *
    {[Date].[Date].[All].[2011].[Q2].[April].[02 Apr 2011]}
    on 0, topcount((Filter({[Employee].[Employee].[Employee].Members} , [Measures].[Sales Amt YTD] <> 0 )),5,([Measures].[Sales Amt YTD] )) on 1 FROM [Sales]


    http://darren.gosbell.com - please mark correct answers

    Monday, June 18, 2012 5:25 AM
    Moderator

All replies

  • This should get the result you are after. But it depends on what client tool you are using. If you are getting a flattened resultset the column will have the measure name and date concatenated. If you are getting back a cellset you should get both values as column headers.

    WITH 
              MEMBER [Date].[Date].[Sales Amt YTD] as SUM(PeriodsToDate([Date].[Date].[Year],[Date].[Date].CurrentMember),
    [Measures].[Sales Amt] 
    ) SELECT {[Measures].[Sales Amt YTD]} *
    {[Date].[Date].[All].[2011].[Q2].[April].[02 Apr 2011]}
    on 0, topcount((Filter({[Employee].[Employee].[Employee].Members} , [Measures].[Sales Amt YTD] <> 0 )),5,([Measures].[Sales Amt YTD] )) on 1 FROM [Sales]


    http://darren.gosbell.com - please mark correct answers

    Monday, June 18, 2012 5:25 AM
    Moderator
  • Thanks Darren for the quick reply.Do you refer to MEMBER [Measures].[Sales Amt YTD] as SUM(PeriodsToDate([Date].[Date].[Year],[Date].[Date].CurrentMember),measures.[sales amt]) instead of [Date].[Date].[Sales Amt YTD] as SUM(PeriodsToDate([Date].[Date].[Year],[Date].[Date].CurrentMember),measures.[sales amt])

    thanks,

    krishna


    krishna

    Monday, June 18, 2012 6:08 AM
  • Thanks Darren for the quick reply.Do you refer to MEMBER [Measures].[Sales Amt YTD] as SUM(PeriodsToDate([Date].[Date].[Year],[Date].[Date].CurrentMember),measures.[sales amt]) instead of [Date].[Date].[Sales Amt YTD] as SUM(PeriodsToDate([Date].[Date].[Year],[Date].[Date].CurrentMember),measures.[sales amt])

    thanks,

    krishna


    krishna

    Yes, sorry I forgot to go back and switch that in the WITH MEMBER. Switching it to the measures means that we can put the [2 April 2011] date attribute directly on the column. 

    http://darren.gosbell.com - please mark correct answers

    Monday, June 18, 2012 6:33 AM
    Moderator
  • Thank you Darren.It worked great..

    krishna

    Tuesday, June 19, 2012 8:54 PM