MDX PriorYear YTD using same date as today.

Question

 

We changed our Fiscal Year this year and we want to compare this year's sales (8/20 is the 235th day of the fiscal year) to last year's sales (8/22/07 is the 235th day of last fiscal year).

 

I have created a member in my Time Dim that indicates what today is.  I have all the Day_ID's for each year in my time dimension table, so I can compare the two.

 

I created a YTD_Sales measure with the expression Sum(YTD([TIME DIM].[SALE DATE].CURRENTMEMBER),[MEASURES].[SALES$])

 

I then created a (Last year to date) LYTD_Sales measure with the expression  (ParallelPeriod([TIME DIM].[SALE DATE].[YEAR],1),[YTD_SALES])   This is using the above YTD_sales expression.

Unfortunately, this gives me the whole ytd sales for last year, and we only want up until what our current ytd sales are, which is to today. 

 

I have seen many posts that take today's date and tried to incorporate that into my mdx, but i am just not familiar enough w/syntax and the language that I either get level or member expression errors or just plain old null values.  I need to do this for MTD, but if I can get the correct syntax for the YTD, I'm sure I can do the MTD from there.

 

I think I tried the SQL2005 Time Intelligence, but for some reason that isn't working, but maybe I'll try it again.

 

Thank you for any help...

 

Answer 1

 

Hi,

 

your YTD_Sales measure is defined like this:

 

Code Snippet

Sum(

YTD( [TIME DIM].[SALE DATE].CURRENTMEMBER ),

[MEASURES].[SALES$]

   )

 

 

Your LYTD_Sales measure should be defined the same way, but with ParallelPeriod in the right place:

 

Code Snippet

 

Sum(

YTD(

ParallelPeriod( TIME DIM].[SALE DATE].[YEAR], 1,

      [TIME DIM].[SALE DATE].CURRENTMEMBER

    )

   ),

[MEASURES].[SALES$]

   )

 

 

For MTD measures just replace YTD with MTD and that's it.

 

Now, about that fiscal shift. You haven't specified anything about that in your LYTD measure. If needed, you can use Lag/Lead functions to move member that is returned by ParallelPeriod function a few items back/forth.

 

Regards,

 

 

Tomislav Piasevoli

Business Intelligence specialist

http://www.softpro.hr

 

 

Answer 2

Thank you for your prompt response...

 

I made the changes suggested and I am receiving the same results as my original LYTD_Sales calculation.  It is still giving me a full year sales $ for last year rather than through just the day_id that is the same for today's day_id.  I also tried the Lag(1), but it still didn't change.  It feels like it's not passing the day_id, but i'm not sure where that would be in the script.

 

I also tried to use the Time Intelligence, but i'm getting all sorts of errors while processing the cube because I'm using a view in my DSV rather than the source table and it tries to add a calculated field and it doesn't like that during processing.  So I'm back to using MDX.

 

 

Answer 3

 

Hi,

 

maybe the problem is that you don't have level types in your time dimension. Check whether you have year level marked as Year type in the properties of your Dimension editor. If so, mark all your levels (attributes) in time dimension appropriately.

 

Regards,

 

 

Tomislav Piasevoli

Business Intelligence specialist

http://www.softpro.hr

Answer 4

I have checked all the properties of the time dimension and they all appear to be setup.  The only thing I can think of that could make this not work is the DayID is not setup w/an attribute relationship, but it is tied to key from the original creation of the dimension. 

 

Any other suggestions?  I am still getting a full year Prior YTD sales$ for either code.

thank you

 

Answer 5

 

Hi,

 

I re-read all posts and now I understand it better. You added a field in named query in DSV where you calculated day_id for each day in year so that you can match the same day in two different years. OK, good. That field can be an attribute in your time dimension related to key of time dimension. If you want, you can make it invisible to end users.

 

Your LYTD_Sales measure can be defined without ParallelPeriod, but rather using day_id and year attributes:

 

Code Snippet

 

Sum(

YTD(

Exists( [TIME DIM].[SALE DATE].CURRENTMEMBER.Level.Members,

   { [TIME DIM].[Day_ID].CurrentMember } *

   { [TIME].[YEAR].PrevMember }

 ).Item(0)

   ),

[MEASURES].[SALES$]

   )

 

 

I presume you have Day_ID and Year attributes in your time dimension. Exists isolates only those members (dates) with same day_id but previous year, returning only one member (it is supposed to be so). YTD should take aggregated value from beginning of its year. I think it should work.

 

If it still doesn't work, try making a calc measure that converts Exists() part to string using MemberToStr() function (add .UniqueName after .Item(0) to diplay members name) and display it in a query. It should return the name of the member from previous year with same day_id. If so, then YTD doesn't work. If not, then relations might be incorrect. If so, explain what attributes you have and how are they related, hierarchies also.

 

My guess is this still won't work because previous MDX didn't work as expected (probably bad relations), but ... let's test it.

 

Regards,

 

 

Tomislav Piasevoli

Business Intelligence specialist

http://www.softpro.hr

 

Answer 6

I did try the code provided, but received some errors and so I tried using my normal Day_ (date format) from my hierarchy where I know all the attribute relationships are setup and the properties of the fields are defined correctly and I am still receiving an error. "The CURRENTMEMBER function expects a hierarchy expression for the 0 argument.  A member expression was used"

 

 

I did come up w/another solution and I just went ahead and used Today's Date rather than the Day_ID from our TimeDim table, due to the fact that whatever I use (Day_ or the Day_ID), it still will not be comparing apples to apples, as today (8/27/08) is in fiscal Month August, but last year (8/27/07) was in Fiscal Month September, so the business decided to use today for our comparison and not worry about it being off a day or two.

 

Anyway my solution was to create a Measure Member called "Todays_Date" and had to format according to what our Time Dim shows for our Day_  (yyyy-mm-dd 00:00:00)

 

Dimension/Hierarchy Structure:

Time Dim (Dimension)

Sale Date (Hierarchy Name)

 Fiscal Month

 Fiscal Qtr

 Fiscal Month

 Fiscal Week

 Day_

 

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[Todays_Date]
 AS Format(vba!now(),"yyyy") + "-" + format(vba!now(),"MM") + "-" + format(vba!now(),"dd") + " 00:00:00",
VISIBLE = 1; 

 

 

 

 

Then I created a Measure called "LastYrs_Date":

 

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[LastYrs_Date]
 AS vba!str((vba!year(vba!now())-1)) + "-" + format(vba!now(),"MM") + "-" + format(vba!dateadd("d",-1,vba!now()),"dd") + " 00:00:00",
VISIBLE = 1  ; 

 

 

**please note I am substacting 1 day as our sales get updated each night, so really today is as of yesterday, so I only pulled thru yesterday's date last year.

 

 

Then I created a CYTD_Sales (Current Ytd Sales) using the new {Todays_Date] created above:

 

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[CYTD_Sales]
 AS SUM(periodstodate([Time Dim].[Sale Date].[Fiscal Year],strtomember("[Time Dim].[Sale Date].["+[Measures].[Todays_Date]+"]")),[Measures].[Sales$]),
VISIBLE = 1;

 

 

   

 

Then I created a PYTD_Sales (Prior Year YTD Sales) using the new [LastYrs_Date] created above:

 

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[PYTD_Sales]
 AS SUM(periodstodate([Time Dim].[Sale Date].[Fiscal Year],strtomember("[Time Dim].[Sale Date].["+[Measures].[LastYrs_Date]+"]")),[Measures].[Sales$]),
VISIBLE = 1;  

 

 

 

I also created CMTD_Sales as:

 

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[CMTD_Sales]
 AS SUM(periodstodate([Time Dim].[Sale Date].[FISCAL Month],strtomember("[Time Dim].[Date].["+[Measures].[Todays_Date]+"]")),[Measures].[Sales$]),
VISIBLE = 1;   

 

 

 

and a PMTD_Sales:

 

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[PMTD_Sales]
SUM(periodstodate([Time Dim].[Sales Date].[FISCAL Month],strtomember("[Time Dim].[Sales Date].["+[Measures].[LastYrs_Date]+"]")),[Measures].[Sales$]),

VISIBLE = 1  ;

 

 

 

Unfortunately, my PMTD_Sales is showing null today, as where I swear it had something yesterday when I calculated the numbers to validate it was correct, but I have this feeling that it's because Fiscal Month Last year doesn't have any sales yet.  I will have to wait for another day to test it again and probably change it over to review the Fiscal Month etc..

 

These calcs will show the same value no matter what level of a date hierarchy we are at, but we have the other drillable ytd/mtd calcs for that.

 

Thanks again for your time!

 

Answer 7

 

Hi,

 

I'm glad you are trying to do something on your own.

 

Few remarks. In my previous post, I wrote [TIME] instead of [TIME DIM]. In your last post you have wrong names on several definitions. [SALE DATE] is sometime written as [SALES DATE], and sometimes as [DATE]. See last to definitions. If you correct it, maybe it will work as wanted. Check everything thoroughly.

 

There's a great way how can you debug such StrToMember parts since they won't report an error, but they'll give you null. Just remove that extra part around that and leave only content of StrToMember() function. That way you'll see what you have. After that, test for conversion of that to a member but add .UniqueName in the end to provide result as string. If you receive null, you wrote wrong argument for StrToMember() function. Then check name again. If that passes ok, other possible errors will be logical (or architectural - types of level for example) or syntax (you'll receive an error like in my last MDX).

 

Regards,

 

 

Tomislav Piasevoli

Business Intelligence specialist

http://www.softpro.hr

Answer 8

 

The [Sales Date], [Date] etc are just typos on my part in this post, I was trying to do this for several different cubes, so when I copied the code, I may have done from different cube, Sorry to anyone trying to follow the code.

 

We ended up having a consultant review this and made some suggestions to make it more efficient:

 

Created a new sets called TODAY and LAST YEAR TODAY... and will only use one cube to copy the code... 

 

In this code, I had to re-format the date to match our data, in another cube, I didn't have to.

Code Snippet

CREATE SET CURRENTCUBE.[Today]

AS STRTOSET("{[Sales Date].[Date].[INVOICE DATE].&["+Format(vba!now(),"yyyy") + "-" + format(vba!now(),"MM") + "-" + format(vba!now(),"dd") + "T00:00:00]}");

CREATE SET CURRENTCUBE.[Last Year Today]

AS Parallelperiod([Sales Date].[Date].[YEAR],1,[today].item(0));

 

 

Used these new sets to calculate the CMTD, CYTD, PMTD, PYTD as..

 

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[CYTD_Sales]

AS SUM(periodstodate([Sales Date].[Date].[Year],[today].item(0)),[Measures].[Net_Sales]),

FORMAT_STRING = "Currency",

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[CMTD_Sales]

AS SUM(periodstodate([Sales Date].[Date].[Month],[today].item(0)),[Measures].[Net_Sales]),

FORMAT_STRING = "Currency",

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[PYTD_Sales]

AS SUM(periodstodate([Sales Date].[Date].[Year],[Last Year Today].item(0)),[Measures].[Net_Sales]),

FORMAT_STRING = "Currency",

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[PMTD_Sales]

AS SUM(periodstodate([Sales Date].[Date].[Month],[Last Year Today].item(0)),[Measures].[Net_Sales]),

FORMAT_STRING = "Currency",

VISIBLE = 1;