Calculate YTD value

[paulette33]

I have a report that pulls that has a condition for one year. I then filter the report to only display the current week. I have a YTD calculation for revenue, which uses the following formula - nofilter(sum(revenue)). This returns the current YTD revenue as there is only revenue data up through the current week. I need to also calculate the YTD plan revenue. I can't use the same formula since the plan data is populated for the entire year and I only want to sum the plan from the beginning of the year through the current reporting week. Any ideas on how this can be done without using a different data provider?

Thanks in advance!

 

[skrandel]

It depends on the granularity of your plan data. If it's at the right granularity, it should be in a different universe context. You should be able to bring it into the report exactly the way you bring in the actuals.

Steve Krandel
Knightsbridge Solutions
formerly BASE Consulting Group

http://www.knightsbridge.com

 

[Subhashab]

Hi paul,
I am not sure i understood ur problem . can you explain with some dummy data.

 

[paulette33]

I'll try to explain this in better detail. The report runs for the current fiscal week. Since I have to do a YTD calculation on the sales, I set my condition to pull in all of 2003 and then filter to display data for the current reporting week. This allows me to create a formula to calculate the YTD sales, i.e. =NoFilter(Sum(<revenue>)). The problem I am having is calculating the YTD plan sales. The universe contains plan data for all of 2003. The YTD calculation needs to only sum plan sales from the beginning of the year through the current reporting week. Because the plan data exists for the entire year, if I do a =NoFilter(Sum(<plan revenue>)), it sums the entire year.

Here's an example:

Current reporting week = 8/3/03
Data for Sales from 2/2/03 - 8/3/03
Data for Plan sales from 2/2/03 - 2/1/04

Report should display sales for week of 8/3/03, YTD sales (which is from 2/2/03-8/3/03), and YTD Plan (which is from 2/2/03 - 8/3/03).

Is there a way to calculate this without having to hard code in the dates? I have a filter that automatically determines the current reporting week using CurrentDate() function.

I hope this provides more information. Thanks!!

 

[pressjanssen]

Hi paul,

I think I have a solution to your problem.
To calculate your YTD plan sales create a new object which returns 1 for all the dates before CurrentDate() and 0 for all the dates after CurrentDate(). Then to calculate your YTD multiply this new object with your YTD formula.
Only the dates before your CurrentDate will be calculated.

I hope this provides more solution


Yan
Personal Consulting

 

[paulette33]

Thank you for your help! I tried what you suggested and it worked!!!! THANKS!!