2 Replies Latest reply: Jun 1, 2010 8:55 AM by jalldridge RSS

    Issue with set analysis and data for last month

    jalldridge

      Hi guys,

      I'm just starting out with QlikView and need to get some figures in a chart for last months activites but am having issues with doing this.

      I have a table of attendance information and I'm loading the following as part of my script:

       

       

       

       

      identifier,
      month (month) as [Month],
      year (month) as [Year],
      month as [Attendance_Date],





      identifier is a unique number given to an individual person - it is possible that the same person is listed in the table several times.

      month is the time stamp when the attendance occurred.

      I also have a number of different locations called SiteName.

      What I want to do is to create a bar chart with the SiteName along the x axis and then the distinct count of individuals who attended last month.

      However my understanding of set Analysis and the expression builder is letting me down. This is what I have in my expression:

       

      Count ( {<Year=, Month= {" =addmonths(monthstart(today(),-1) "}>} distinct identifier )



      Any idea on what I'm doing wrong greatly appreciated!

      Thanks

       

       

       

       

        • Issue with set analysis and data for last month
          John Witherspoon

          When using a single value, I'd enclose it in single quotes to indicate a literal. Double quotes indicate a search string, and while searching should give you the same results, I'd use a literal here.

          I think you also need to use dollar sign expansion, $(=...). Otherwise, the system will, I believe, literally search for your expression, rather than the RESULT of your expression.

          You'll need to include an Attendance_Date=, so that that field is ignored as well.

          You have unbalanced parentheses on the addmonths() part of the expression. You need one more closing parenthesis after today().

          =addmonths(monthstart(today()),-1)

          Now put that in a text object. For my date format, it returns '04/01/10'.

          You've used the month() function to establish your Month field. So try creating a month() for comparison:

          =month(date#(20100401,'YYYYMMDD'))

          It returns 'Apr' on my computer. Well, 'Apr' and '04/01/10' aren't the same. Of course, you might think it's still doing some underlying numeric comparison. It isn't, but for the sake of argument, what if it was? We can see that by wrapping both of the above expressions with num(). I get this:

          num(addmonths(monthstart(today()),-1)) = 40269
          num(month(date#(20100401,'YYYYMMDD'))) = 4

          Not only do they look different, they really ARE different. The first is a date. Dates are store as the number of days since December 30, 1899. The second is just a month, with the day and the year stripped out of it.

          What about month(addmonths(monthstart(today()),-1))? That DOES give us a month, so it would be comparable. That's a start, but it would give you EVERY April, not just April 2010. We could use even more set analysis to make sure we had the right year (after all, today() could be in January, in which case we want December of LAST year), with something like year(today)+(month(today())=1). It might look a litle funny, but true = -1 and false = 0, so that expression will subtract 1 from the year if today is in January. Put it all together, and you get something like this:

          count({<Attendance_Date=
          ,Month={'$(=month(addmonths(monthstart(today()),-1)))'}
          ,Year={'$(=year(today)+(month(today())=1))'}
          >} distinct identifier )

          But what I'd probably do instead is a direct date comparison rather than a month and year comparison. For that, add a MonthYear field to your table:

          date(monthstart(month),'MMM YYYY') as MonthYear

          And then do something like this:

          count({<Attendance_Date=,Month=,Year=
          ,MonthYear={'$(=date(addmonths(monthstart(today(),-1),'MMM YYYY'))'}
          >} distinct identifier)

            • Issue with set analysis and data for last month
              jalldridge

              Thanks for the response and outlining how set analysis works.... Not quite as easy as I first thought....

              I seemed to be having some funnies with my loaded month, year descriptions causing issues but changing them to something more unique and using the following appears to work :-)

              Count ( { < Attendance_Month = {$(vLastMonth) },
              Attendance_Year={'$(=Year(today())+(Month(today())=1))'}
              > } distinct identifier )