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,

assuming you have a "Day" field, try

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

Regards,

Stefan

[#3]

with last YTD that's what i did but it's not giving me the result till from beginning of last year till same day as today this year!

sum({$<Year = {$(=max(Year-1))}, Month = {">=$(=min({<Year={$(=max(Year-1))}>} Month)+1) <=$(=max({<Year={$(=max(Year))}>} Month))"},Day={"<=$(=day(today()))"}>} SalesDetails.CSEXPR - SalesDetails.CSEXVT)/1500

any idea why??!

[swuehl]

Hi layal,

I am not sure what you want to achieve with that expression:

1) Do you really want "max(Year-1)" or "max(Year)-1" ?

2) Then, why do you limit the Month to the min value?

Regards,

Stefan

[#5]

hello stefan,

1) i need the max of last year

2) i'm choosing min to get the minimum month of the year

anyway to make it clear i need to get the Last YTD starting february till current date of today

any idea??

regards,

layal

[#6]

by the way the following expression is working,it's giving me the right result starting february last year but till the "END" of the same month of this year.and what i need is till the same day not till the same end of month

sum({$<Year = {$(=max(Year-1))}, Month = {">=$(=min({<Year={$(=max(Year-1))}>} Month)+1) <=$(=max({<Year={$(=max(Year))}>} Month))"}>} SalesDetails.CSEXPR - SalesDetails.CSEXVT)/1500

[swuehl]

Hi layal,

I tried to build a small .qvw

I think the calculation is correct to the current day last year. Please check.

Regards,

Stefan

[swuehl]

Hi layal, it's me again.

Please note that your expression is depending on current selection (previous year of max. selected year).

If you need a fixed last year as of today (i.e. 2010), you could use

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

Is starting the YTD with last years min month +1 (Februar in this example, but depending on data) a business requirement?

Regards,

Stefan

[#9]

hello stefan,

i checked your qvw file.i used the same expression as yours but i'm not getting the right result regarding Last YTD.

it's as if it's getting the sum of the sales <=4days of each month.

their year start on february the 1st of each year and not on january that why i'm using min(month + 1)

[swuehl · Accepted solution]

Hi layal,

ups, you got me. My expression will limit the evaluation to days of months < current day of months.

That's absolutely nonsense, of course.

I guess you have a date field, too. (if not, that could be created during load, see my example).

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

should work, now.

Sorry for my mistake, I think I need some sleep,

Stefan