4 Replies Latest reply: Jan 13, 2012 3:59 AM by Dennis Hoogenboom RSS

    Help needed with a Set-analys

    Göran Hofstedt

      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
          Dennis Hoogenboom

          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
              Göran Hofstedt

              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?