Year To-date Report question

[#1]

Hi,

I currently have a year-to-date report linked in to a basic calendar.

If I wanted to see all data from 01/January/2012 to 03/March/2012 I would select from the calendar:

Year: 2012

Months: Jan/Feb/Mar

Days: 01/02/03

The expression I use for the Dimension:

if(Month = MM, if(Day2 <= Day, PKid, NULL), PKid)

MM is a Varibale set as: =Max(Month)

Day = Calendar

Day2 = Day of the Date within the data

PKid = unique key for the line of data

This works; however my data sets are now starting to get large and I am experiencing performance issues.  Does anyone know of a better way to get the same result?

Many thanks

[#2]

Hi

This is a little tricky to explain but I'll try my best!!

I use two calendars, one is the reporting calendar and contains the dates that the end user selects from, this calendar joins to another calendar, it contains a full copy of the calendar for each date in the reporting calendar, so if you have two years of dates in your reporting calendar then that's approx 730 dates. My second calendar would contain 730 x 730 dates, so a total of 532900 dates.

The "trick" if you like is that the second calendar includes a series of 1/0 flags that identify if the date belongs to a certain group (in your case YTD) and I include lots of flags to identify YTD, Prior YTD, Prior Year, QTD, Prior QTD, MTD, WTD, Day, Previous Day etc etc.

So what you'd see when showing the data in a table is, for example

RepCal          FactCal                                         

RepDate         RepDate     FactDate   YTDFlag   TodayFlag      

1.3.12            1.3.12          5.3.12          0               0

1.3.12            1.3.12          4.3.12          0               0

1.3.12            1.3.12          3.3.12          0               0

1.3.12            1.3.12          2.3.12          0               0

1.3.12            1.3.12          1.3.12          1               1

1.3.12            1.3.12          28.2.12        1               0

And so on back to the start of the calendar.

Then your reporting calendar would have the next date which joins to every date, but with slightly different flags set.

Then, finally, your expressions are now simple and very efficient, all you have to do to get a YTD value is:

Sum(Amount*YTDFlag)

Hope I managed to explain this well enough..,

[#3]

Does this mean you essentially join a calendar onto the main data and then create a link from that table onto another calendar?

[#4]

Yes that's the idea, the calendar that joins to your fact table has many copies of itself in the same table, it will then join to a copy of the calendar which only includes each date once, the users should select from the outer calendar.

When the user selects a date, this will reduced (filter) the big calendar to just a single copy of all dates, but the key thing is that then they'll have correct flags available for each period.

It sounds complex but it's not, and it performs well.

Best wishes

Nigel West.