Syntax for set analysis with dates

[#1]

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

[sunny_talwar]

May be this:

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

[Anonymous]

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))

[#4]

Unfortunately, Qlik doesn´t recognize this

[Anonymous]

use addyears instead of addmonths

addyears(KeyDate,-1)

[#6]

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?

[rubenmarin]

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))

[Anonymous]

thks for the info

[berryjulien2]

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))

[#10]

Thanks Ruben & Rudolf,

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