set analysis YTD calculation

[#1]

Hi,

I am using this expression to calculate sum of revenue for previous year

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

but i want this expression to show same period revenue for previous year as showing in current year

for ex  if in 2015 there is only one month January it will be compare to jan month of 2014

and if there are three months in 2015 it will compare to same three months of 2014 

[jagan]

Hi,

Try like this if you have a date field

For YTD

=sum({$<Year=,Month=, DateFieldName={'>=$(=YearStart(Today()))<=$(=Today())'}>}Revenue)

For Previous Year YTD

=sum({$<Year=,Month=, DateFieldName={'>=$(=YearStart(Today(), -1))<=$(=AddYears(Today(), -1))'}>}Revenue)

Hope this helps you.

Regards,

Jagan.

[kavita25 · Accepted solution]

For Current year

sum({<Year={$(=Only(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"}>}Revenue)

For Previous Year

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

If You are using Master Calendar you can create flag for the same.

For  Current Year YTD

if(if(Month(date(Floor(Date)))<=3,(Year(date(Floor(Date)))-1)&'-'&(Year(date(Floor(Date)))),(Year(date(Floor(Date))))&'-'&(Year(date(Floor(Date)))+1))=if(Month(((today())))<=3,(Year(((today())))-1)&'-'&(Year(((today())))),(Year(((today()))))&'-'&(Year(((today())))+1)),1,0) as YTD_Flag,

For Prior Year YTD

inyeartodate(Date, $(vDateToday), -1,4)* -1  AS LastYTDFlag

Hope it helps you

Regards,

Kavita

[#4]

Thanks Jagan

That was helpful but it is not working for 2014 or 2013 bcz  of today() can i replace this with my transactiondate?

pls suggest

[jagan]

Hi,

This will work

=sum({$<Year=,Month=, DateFieldName={'>=$(=YearStart(Today(), -1))<=$(=AddYears(Today(), -1))'}>}Revenue)

because I am using =YearStart(Today(), -1) this will give Jan-1-2014 and =AddYears(Today(), -1) will give Jan-16-2014

I am not sure why it is not working for you.  Are you selecting any other date fields other  than Year=,Month=?  If yes then you have to exclude the selections by using (Quarter=), etc.

Regards,

Jagan.

[nikhilgarg]

HEy,

You mean to say either go with the above given script or with Master calendar script. IS it so ?

[kavita25]

Yess.....

Above given script can be used directly in the expression, which is little bit complex.

And the same can be achieved by creating flags and directly can be use in expression which is simpler than the writing whole expression.

[nikhilgarg]

Ok . Actually for me below script is easier than master calendar script.

For Current year

sum({<Year={$(=Only(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"}>}Revenue)

For Previous Year

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

[kavita25]

Yeah....good for you.

[Pdhondi]

am using this  script 

Sum({<Year={$(=Only(Year))}>}Sales)

but the result is 0