Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Last YTD calculation issues.

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

1 Solution

Accepted Solutions
maximiliano_vel
Partner - Creator III
Partner - Creator III

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

View solution in original post

6 Replies
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
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

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
Partner - Creator III
Partner - Creator III

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

Regards

Not applicable
Author

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

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

Not applicable
Author

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

Thanks