topic Re: Syntax for Set Analysis comparing Last Year to Date in QlikView

Syntax for Set Analysis comparing Last Year to Date

Mon, 04 Nov 2013 12:32:24 GMT

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.

Re: Syntax for Set Analysis comparing Last Year to Date

Mon, 04 Nov 2013 12:39:51 GMT

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

Re: Syntax for Set Analysis comparing Last Year to Date

Gysbert_Wassenaar — Mon, 04 Nov 2013 12:46:23 GMT

Maybe you need to match the date format of Datum:

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

Re: Syntax for Set Analysis comparing Last Year to Date

Mon, 04 Nov 2013 12:52:31 GMT

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.

Re: Syntax for Set Analysis comparing Last Year to Date

Mon, 04 Nov 2013 12:54:34 GMT

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)

Re: Syntax for Set Analysis comparing Last Year to Date

Gysbert_Wassenaar — Mon, 04 Nov 2013 12:59:58 GMT

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

Re: Syntax for Set Analysis comparing Last Year to Date

Mon, 04 Nov 2013 13:11:42 GMT

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!

Re: Syntax for Set Analysis comparing Last Year to Date

Mon, 04 Nov 2013 13:49:21 GMT

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.