Help needed with a Set-analys

[#1]

Hi. I hope anyone can help me with a SET-analys (I think I´s a set-analys...) that I´m not even can figure out were to start with.. =(

I have 4 listboxes

1) StartDate (YYYY-MM-DD)

2) StopDate (YYYY-MM-DD)

3) UserType

4) UserID

To this I have connected a calender with year and months. CalendarYear and CalendarMonthName

When I select a month and year I can se how many users that have a StopDate and StartDates, I get the result in a pivot by the simple expression

=Count(StartDate)

My problem is that I cant figure out how to get how many that are active/ have a startdate but not a enddate. Can someone help me out with this?

Thanks!

[Anonymous]

I understand your problem. You are trying to select something that isn't there.

If you have a startdate of 01-01-2010 end an enddate of 31-01-2010 you want it to count it as active if you select for example 12-01-2010 ,right?

There are a few ways to do this. One is to create a new field in your script (activedate) which is genereate for every day between the startdate and the enddate. That way you have select and count on that field.

An other way is to you a macro and a button which will make a selection (from-till) on one or more fields.

Take a look at the atteched file for an example.

Also : http://community.qlik.com/message/169783#169783

[#3]

Thanks for the help. I tryed that but I did´t fully worked but I think why (but don´t know any solution)

When I load the script I have to do like this to make the date-field to be a actually date,

Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD') as StopDate

That because the date are just a string with numbers, 20120131 and QW wont reconise it as a date if a don´t do this.

So I have also looked at the sourcefile and the field with no stopdate are actually printed as 9999999999

But when I do the Date(Date#.... the posts with 999999 disapears. Anyone that knows how to overcome this?

[#4]

hmm, I was able to get the 999999999 posts as well the dateformated post by load the table twice were I use the Date(Date#..function in the last load...

LOAD
   StopDate

FROM
[C:\Qlikview Filer....

Load
Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD') as StopDate

FROM
[C:\Qlikview Filer\.....

I don´t look pretty so any suggestion how to make it easier are welcome!

[Anonymous]

Or what you could do somthing like below in your load scrip is:

Load

...

If(StopDate = '99999999' , 2999-12-31 , Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD')) as StopDate,

....

From ...

This way it will change '99999999'  into 2999-12-31