Offsetting Dates

[#1]

I am trying to do a comparison of this year sales to last year sales, YTD,

My data however gets generated over night to include up to the previous days sales.

So if comparing to last year to date, I have to offset the results by one day. I came up with this expression but I am convinced it is not right,

sum(if(yeartodate([INVC DATE]-1,-1), [TOT SALES])

What do you think?

[fernandotoledo]

Yeah, it´s not ritght : )

This expression offset is set in years, so its returning the ytd sales for the last year!

You can make calculations with dates like integers:

sum(if(inYearToDate(Sales_Date, (today()-1) ,0), Value)

For expressions that needs an offset of weeks, months there is a group of functions (addmonth(), addyear(),etc) that does the job!

Hope it helps!

Fernando

[#3]

I was trying to get YTD for the previous year, but I want YTD of last year less one day.

If i were to go into my document today, my YTD data is up to June 17, 2010 .

I want my expression to give me comparison of YTD up to June 17, 2009

[fernandotoledo]

Then you can combine the expressions:

Today()-1 => Previous day

inYearToDate(...,..., -1) => Previous Year

[#5]

ok and to take it one step further, if i want month to date of last year less one day, what should my expression look like?

[fernandotoledo]

You have a bunch of Date & Time functions! In this case it would be the InMonthToDate() wich works like the year to date one.

It´s worth to look for 'Date and time' in the help to see them all!

best regards

Fernando