topic Re: Help needed with a Set-analys in QlikView

Help needed with a Set-analys

Thu, 12 Jan 2012 10:37:04 GMT

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!

Re: Help needed with a Set-analys

Anonymous — Thu, 12 Jan 2012 12:21:12 GMT

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

Re: Help needed with a Set-analys

Thu, 12 Jan 2012 18:44:34 GMT

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?

Re: Help needed with a Set-analys

Thu, 12 Jan 2012 19:02:02 GMT

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!

Re: Help needed with a Set-analys

Anonymous — Fri, 13 Jan 2012 08:59:28 GMT

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