Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Issue with set analysis and data for last month

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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)

View solution in original post

2 Replies
johnw
Champion III
Champion III

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)

Not applicable
Author

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 )