Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
yesterday

Answered yesterday

  • Sunday, March 17, 2013 6:57 AM
     
     
    Sign In to Vote
    0

    hi,

    i want to run an mdx code that will always return the yesterday date every time i run it, and everything i tried doesnt work.

    so what do i need to write instead of:

    select {[Time].[Year - Month - Period - Date].[Date].&[2013-03-10T00:00:00]} on columns

    thanks in advance :)

     

All Replies

  • Sunday, March 17, 2013 8:18 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi astridaku ,

    You can use the date's functions to achieve it . Please try the next code :

    with member yesterday as dateadd("d",now(),-1)
select {yesterday} on 0 from [Cube]

    Regards, David .

     
  • Sunday, March 17, 2013 9:27 AM
     
     
    Sign In to Vote
    0

    hey daviv, thanks for the reply, i did add the new code on top of the other members. but i am getting the following error.

    Executing the query ...
    The Measures hierarchy already appears in the Axis0 axis.
    Execution complete

    here is my code (and i know my mdx is too basic :()

    with 

    member yesterday as dateadd("d",now(),-1)

    member [Measures].[Max] As
    max({[Hour Dim].[D Hour].[All]},[Measures].[Purchase - converted to USD])

    member [Measures].[Min] As
    min({[Hour Dim].[D Hour].[All]},[Measures].[Purchase - converted to USD])

    member [Measures].[Avg] As
    avg({[Hour Dim].[D Hour].[All]},[Measures].[Purchase - converted to USD]/24)

    select (yesterday) on 0,
    {[Measures].[Purchase - converted to USD],
    [Measures].[Purchases_Count],
    [Measures].[Site Pay User],
    [Measures].[Free_signups],
    [Measures].[free to site pay user],
    [Measures].[click to site pay user],
    [Measures].[Click Count],
    [Measures].[Guests Logins Count],
    [Measures].[Guests Unique Logged in],
    [Measures].[Guests Credit Spent],
    [Measures].[Max],
    [Measures].[Min],
    [Measures].[Avg]} on rows 
    FROM [cube] 

     
  • Sunday, March 17, 2013 10:50 AM
     
     
    Sign In to Vote
    0

    Hi astridaku ,

    The error tells you you need to put the Measures hierarchy on the rows OR on the columns, you can't mix between the two :)

    Did it help ?

    Regards, David .

     
  • Sunday, March 17, 2013 10:58 AM
     
     
    Sign In to Vote
    0

    oh mamma... but i am putting the date on column and the measures on rows.

    what am i missing? :(

     
  • Sunday, March 17, 2013 11:04 AM
     
     Answered
    Sign In to Vote
    0
    You are right !! You can put dim members/attributes on one axis, and the Measures on the other one . Maybe you want to consider holding a 'yesterday' attribute in you date dimensions, and then you can place them in different axis .. Hope it helps :)

    Regards, David .

     
  • Sunday, March 17, 2013 11:40 AM
     
     
    Sign In to Vote
    0
    thanks!
     
  • Sunday, March 17, 2013 2:12 PM
     
     
    Sign In to Vote
    0
    Please mark as answered if it helped, so others will use the answer :)

    Regards, David .

     
  • Monday, March 18, 2013 3:32 AM
     
     Answered
    Sign In to Vote
    0

    Yesterday attribute in the date dimension is a really useful idea.  I use that with many of the date periods, so users can select relative dates very easily.

    However if you simply want to change the following mdx to always select yesterday

    select {[Time].[Year - Month - Period - Date].[Date].&[2013-03-10T00:00:00]} on columns

    Then try this (or you might be able to write it more elegantly using formatdate()

    select

    StrToSet("{[Time].[Year - Month - Period - Date].[Date].&[" + cstr(year(now()-1))
    +"-"+right("0"+cstr(Month(now()-1)),2)
    +"-"+right("0"+cstr(Day(now()-1)),2)
    +"T00:00:00]}")

    on columns

    Hope that helps

    http://RichardLees.blogspot.com 

    Richard