Last YTD calculation issues.

[#1]

I currently have YTD & Last YTD working fine.

For Last YTD - I have all details through the year.  However, on YTD - I am always a month behind.

For Example - today is July 31st.  But I don't have any numbers yet for the current year. 

So  - my YTD is showing Jan1 - June 30th.

And Last YTD is showing Jan1 - July 31.

How do I get Last YTD to only calculate to the latest YTD Date I have?

Thanks

Amanda

[sunny_talwar]

How are you calculating your Last YTD? Is it done in the script or on the front end using set analysis???

[maximiliano_vel · Accepted solution]

If you  have a master calendar that the last date is June 30/2015 the you could have 4 variables

vMaxYear = Year(Max(Date));

vMaxDate = Max(Date); //Should be June 30/2015

vPrevYear = Year(AddYears(Max(Date), -1))

vMaxDatePrevYear = AddYears($(vMaxDate), -1) //Should calculate to June 30/2014

Then your formula should be something like

LYTD = Sum({<Year={$(vPrevYear)}, Date={"<=$(vMaxDatePrevYear)"}>} Sales) //Accumulated figures up to June 30/2014

YTD = Sum({<Year={$(vMaxYear)}, Date={"<=$(vMaxDate)"}>} Sales) //Accumulated figures up to June 30/2015

[#4]

I am on hold for a couple days...because now I need to put July data in there to test your variables/formula.

I am assuming the variables go into the script?

[maximiliano_vel]

You have to create the variables in Settings->Variable Overview... or (Ctrl + Alt + V)

Regards

[#6]

Sorry this took so long.... But this isn't working. 

Using the varibles and the formual above, I only get '-'

[#7]

Took me forever to get time to work on this project again.  But your answer above was the key!

Thanks