topic Re: Help with a set in QlikView

Help with a set

Thu, 29 Mar 2012 14:02:45 GMT

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!

Help with a set

Thu, 29 Mar 2012 14:07:18 GMT

Try something along the lines of this:

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

Help with a set

jagan — Thu, 29 Mar 2012 14:39:12 GMT

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.

Re: Help with a set

Thu, 29 Mar 2012 18:21:25 GMT

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?

Re: Help with a set

sebastiandperei — Thu, 29 Mar 2012 20:02:54 GMT

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

Re: Help with a set

Fri, 30 Mar 2012 08:55:44 GMT

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)