topic Re: Syntax for set analysis with dates in QlikView

Syntax for set analysis with dates

Mon, 19 Oct 2015 14:40:56 GMT

Good afternoon,

In a chart, I use expression

count({<JJJJ.MM={"$(KeyDate)"}>}distinct(EmployeeNr))

where I let the user enter the KeyDate (say 2015.12) via an InputBox. So far, so good. Now how can I build expressions for the previous years i.e. 2014.12, 2013.12, etc? Somehow I don´t get the syntax right...

Thanks in advance

Re: Syntax for set analysis with dates

sunny_talwar — Mon, 19 Oct 2015 14:45:20 GMT

May be this:

=Count({<JJJJ.MM={"$(=AddYears($(KeyDate), -1))"}>}DISTINCT (EmployeeNr))

Re: Syntax for set analysis with dates

Anonymous — Mon, 19 Oct 2015 14:47:33 GMT

if JJJJ.MM is your fieldname for year and month you may use

count({<JJJJ.MM={"$(=date(addmonths(KeyDate),-12),'JJJJ.MM')"}>}distinct(EmployeeNr))

if (KeyDate) is in a stringformat, you Need to tranform it to a dateformat before

count({<JJJJ.MM={"$(=date(addmonths(date#(KeyDate),'JJJJ.MM'),-12),'JJJJ.MM')"}>}distinct(EmployeeNr))

Re: Syntax for set analysis with dates

Mon, 19 Oct 2015 14:47:58 GMT

Unfortunately, Qlik doesn´t recognize this

Re: Syntax for set analysis with dates

Anonymous — Mon, 19 Oct 2015 14:49:10 GMT

use addyears instead of addmonths

addyears(KeyDate,-1)

Re: Syntax for set analysis with dates

Mon, 19 Oct 2015 14:59:24 GMT

That looks plausible but unfortunately, it doesn´t work. I defined the key date in an input box (as opposed to a proper variable in the script) and selected unter tab "number" number format settings "date" with format pattern YYYY.MM - any suggestions?

Re: Syntax for set analysis with dates

rubenmarin — Mon, 19 Oct 2015 15:09:16 GMT

Hi Leonardo, one of the expressions of Rudolf applies the date data type to the variable value (just change JJJJ to YYYY):

count({<JJJJ.MM={"$(=date(addmonths(date#(KeyDate),'YYYY.MM'),-12),'YYYY.MM')"}>}distinct(EmployeeNr))

Re: Syntax for set analysis with dates

Anonymous — Mon, 19 Oct 2015 15:13:02 GMT

thks for the info

Re: Syntax for set analysis with dates

berryjulien2 — Mon, 19 Oct 2015 15:20:33 GMT

If I where you, i would used to different variable (one for the month and the other for the year). And after you can use this kind of setanalyses:

count({<JJJJ.MM={"$(=date(addyears(date#(makedate($(V_Year),$(V_Month)),'JJJJ.MM'),-1),'JJJJ.MM')"}>}distinct(EmployeeNr))

or you can do like @Rudolf Said:

count({<JJJJ.MM={"$(=date(addyears(date#($(KeyDate)),'JJJJ.MM'),-1),'JJJJ.MM')"}>}distinct(EmployeeNr))

Re: Syntax for set analysis with dates

Mon, 19 Oct 2015 15:37:38 GMT

Thanks Ruben & Rudolf,

This is so funny, it just doesn´t work!

Re: Syntax for set analysis with dates

rubenmarin — Tue, 20 Oct 2015 07:04:47 GMT

Maybe using the JJJ.MM string and I had another typo: remove the extra ')' in date# function, it should be:

count({<JJJJ.MM={"$(=date(addmonths(date#('$(KeyDate)','YYYY.MM'),-12),'YYYY.MM')"}>}distinct EmployeeNr)

Or using AddYears():

count({<JJJJ.MM={"$(=date(AddYears(date#('$(KeyDate)','YYYY.MM'),-1),'YYYY.MM')"}>}distinct EmployeeNr)

Not tested, if you can upload a sample I can try with real data.