Syntax for Set Analysis comparing Last Year to Date

[#1]

Hi All

I am trying to make a Set Analysis comparing sales for Last Year to Date. I have created a variable that show the date for last year. This variable is called vLYTD.

This variable is 2012-11-04 today when using the date() function otherwise 41217.

I am trying to make a set analysis for this variable but cant get the syntaxt to work. I think this has to do with the formating. At the moment I have written the line as following.

sum({$<Year={$(#vLastYear)},Datum={"<=$(#vLYTD)"}>}Budget),

Where Datum should only make the summation for all records up to 2012-11-04.

Any help in solving this is appreciated.

[#2]

Hi Fredrik,

Try this

sum({$<DateNum={">=Num(YearStart(Today()))"} Budget)

Your variable can be like this vLYTD= Num(YearStart(Today())) then you can use this variable in the set analysis i have shared

[Gysbert_Wassenaar]

Maybe you need to match the date format of Datum:

sum({$<Year={$(#vLastYear)},Datum={"<=date($(#vLYTD),'YYYY-MM-DD')"}>}Budget)

[#4]

Thank you.

I managed to solve the problem though. I formated the variable to be in dateformat where I create the variable and it solved the problem.

[#5]

Thank you, I solved it though in the way below.

vLYTD=Date(today()-365)

sum({$<Year={$(#vLastYear)},Datum={'<=$(vLYTD)'}>}Budget)

Before, I it was written,

vLYTD=Date(today())-365

sum({$<Year={$(#vLastYear)},Datum={'<=$(vLYTD)'}>}Budget)

[Gysbert_Wassenaar]

Subtracting 365 may trip you up with leap years. You can use the addyears function instead: vLYTD = date(addyears(today(),-1))

[#7]

Fredrik,

Have you tried using the Time Chart Wizard from the Tools menu ?

This is a guide that helps you build a year on year comparison without the need of typing in any script. If needed, once you have completed the step you can go back to the object's properties and edit the expressions.

Good luck!

[#8]

Thank you,

That works fine. I didnt know about that function and cant find it in the manual or help function. I know abou the leap year thing and was going to look into that and solve it when I got confirmation that the client wanted that comparison in their application. Now it is solved so that is very good.