YTD till current day

[#1]

hello i'm calculating YTD

i'm using the following in my expression

sum({$<Year = {$(=max(Year)-1)}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} LineSalesAmount)

this expression gives me YTD of lest year till the end of the month.I need to add the day till today.how can i add it in my expression??

thank you:)

[swuehl]

Hi layal,

another comment:

If customer has no data for january of a certain year,data starting in Feb, min(month + 1) would result in March, wouldn't it?

And talking about January, this month will never be taken into account then? (Because you start summing up the values not before February and stop with current month, maximum Dezember)

So in month January 2011, does this count to previous year 2010, then?

[I am not sure if I make myself clear, I think I go home now]

Regards,

Stefan

[#3]

hello stefan,

the customer always has data in january.in example if today is 04/07/2011 then YTD begins on 01/02/2011 till 04/07/2011 and LYTD begins on 01/02/2010 till 04/07/2010.

january is going to be the last month of the year not december.

[#4]

thanks for the help Stefan

as u said it works using the date...

best regards,

layal

[swuehl]

Hi layal,

I am glad to help you.

Being a little refreshed this morning, I think my expression is not absolutely correct:

=sum({$<Year = {$(=year(today())-1)}, Month = {">=$(=min({<Year={$(=year(today())-1)}>} Month)+1) <=$(=max({<Year={$(=year(today()))}>} Month))"},Date={"<=$(=date(today()))"}>} Value)

will calculate the YTD last year, but not till today, but until end of month. In my example, you get 700 for the YTD last year column, but correct is 600 (disregarding the value of 15.7.2010).

I think correct ist:

=sum({$<Year = {$(=year(today())-1)},

  Month = {">= 2"}, //$(=min({<Year={$(=year(today())-1)}>} Month)+1)"},

  Date={"<=$(=date(makedate(year(today())-1,month(today()),day(today()))))"},

  Day = >}

  Value)

{I cleared the Day selection and limited Date to dates < same Date last year, not < today, you see the difference?}

But on another side, I still don't think that our calculation in case of january is correct (in case of a January date as today), It will not be taken as last month of a financial year correctly.

So I worked out a much better solution (in my opinion) for YTD last year:

=sum({1} if (InYearToDate (Date, today(), -1 , 2),  Value))

This uses InYearToDate to determine if a given Date is to be taken into account (third parameter -1 for last year, fourth parameter 2 for financial year starts in February). I used set expression {1] to get all values, disregarding current selection, but you can easily change this.

See also attached example (where you can also set a date as reporting date in input field to play around or select the max. Date).

Hope my thought were correct and this is of any help, I learned a lot working on your problem 😉

Cheers,

Stefan