Help with a set

[#1]

Hi

I´m trying to do a Set that gives me "reactivated" peoples. table PeopleID. I have a table with EndDate and one with StartDate. I want to get those with a Enddate =Year(Today()-1) and startdate =Year(Today())

Do I have to make some variable in the script for this? I was trying with the set/script generator at qlikblog but have not been sucessful.

Can anyone help me? Would be greatfull!

[#2]

Try something along the lines of this:

Count({<Enddate= {"$(=year(today())-1)"},Startdate= {"$(=year(today()))"}>} PeopleID)

[jagan]

Hi,

Try like this

Count({<Enddate= {'$(=(Year(Today())-1))'},Startdate= {'$(=Year(Today()))'}>} PeopleID)

Year(Today())-1 will give you the previous and assigning it to Enddate

Year(Today()) will give you the current year

Hope this helps you.

Regards,

Jagan.

[#4]

Hmm, I don´t get it to work. It don´t calculate the Start/enddate=...Year(today).... right, The start and end-date are in the format of 2010-12-01

I also have a Year field from the calendar, if I only wright Count({$<Year= {'$(=(Year(Today())-1))'}>} PeopleID) I get all the PeopleId that exist last year so the expression by it self works well.

Can I someway add Year(startdate) in the set?

[sebastiandperei]

Hi.

If I understood well, you have two fields: EndDate and StartDate, in a table with PeopleID field too. You want to know how many people ends last year, but begin again current year. Also, EndDate and StartDate aren´t in Date dual format, but only in string "YYYY-MM-DD" Format. (if it's like that, why you said in first post that EndDate=Year(Tioday()-1)? this will give you only year number in EndDate)

If it's ok, you don't need to compare years, but yes complete dates. In the script, if EndDate and StartDate were in date format, you could do:

Count({$<     EndDate= {"<=$(=(Date(Today()-1,'YYYY-MM-DD'))"},

                       StartDate= {">=$(=(Date(Today(),'YYYY-MM-DD'))"} 

                >} PeopleID)

Please, confirm the format of the fields EndDate and StartDate, because if it were =Year(Today()) style, Jagan and Marc are right...

[#6]

Hi

I may have misunderstod the use of year(today()).. but I have got it to work when in script make Year(Startdate) as startdateyear and same for enddate and then use them in the set as

Count({<enddateyear=P(enddateyear),InsatsAvslutadÅr=P(InsatsAvslutadÅr),startdateyear=P(startdateyear)>} Distinct peopleid)