Hi Jerome,
There are several issues there... Let me uncover some and give you some ideas:
1. When you add a Date condition to your Set Analysis, such as Date = {...}, that effectively overrides your Date selection in a filter pane. If you want the formula to be responsive to your date selection, you should formulate it in a different way - for example, use *= instead of a simple =. This way, you will get an intersection between your selected dates and the results of the Set Analysis condition.
2. It looks like you are using the automatic calendar. In this case, you should use a fully qualified field name in your set analysis filter:
date.autoCalendar.Date = {...}
3. Your search condition contains the following:
date.autoCalendar.Date(MonthStart(Max(date.autoCalendar.Date)))
This is syntactically incorrect - the function Date() that you are applying should not be fully qualified. In fact, it's not needed either - the function MonthStart already returns a fully formatted date. So, you can replace that with this:
MonthStart(Max(date.autoCalendar.Date)))
4. Then, you need to decide if this formula should return results for the last selected month or for the current month, disregarding of the selections. For the latter, you should ignore user selections when you calculate Max of date:
MonthStart(Max({1} date.autoCalendar.Date)))
5. When using Dates in Set Analysis search expressions, they need to be enclosed in single quotes, otherwise Qlik will try to calculate them as a mathematical expression.
To bring it all together, I'd formulate the formula as follows. It will return results for the selected month (all dates in the highest month that was selected, not only for the selected date):
Count({<date.autoCalendar.Date = {">= '$(=MonthStart(Max(date.autoCalendar.Date)))'<='$(=MonthEnd(Max(date.autoCalendar.Date)))'"}>}data_id)
Now, with this in mind, the same formula for the previous month would look like this (-1 is the second parameter for the functions MonthStart and MonthEnd:
Count({<date.autoCalendar.Date = {">= '$(=MonthStart(Max(date.autoCalendar.Date), -1))'<='$(=MonthEnd(Max(date.autoCalendar.Date), -1))'"}>}data_id)
Join us at the Masters Summit for Qlik in Orlando or in Dublin, and learn advanced Set Analysis directly from me, along with data modeling, scripting, visualizations and peformance, delivered by some of the best Qlik experts in the world!
