Hi Steve, this is my first try using Qlik Sense ... and at once the first Qlik Sense feature (or bug ?!?) I found...
First, you're using the wrong datefields , for example [A_RequestDate] is not the same field as used in your listbox...
(is it called listbox in Sense?!?, I just call it listbox)
Your listbox uses [A_RequestDate.autoCalendar.YearMonth]
OK, I thought no problem just use [A_RequestDate.autoCalendar.YearMonth] instead of [A_RequestDate] within your set expression.
Surprisingly using auto calendar fields within set analysis or functions like getselectedcount() doesn't work for me at all. (using QlikSense Desktop 3.1 SR6, same problem with 3.2 SR2) . A short time I believed that I didn't understand anything at all about set analysis
Finally I had to create separate YearMonth Fields like that:
MonthName(Date(A_RequestDate ,'DD/MM/YYYY')) as A.YEARMONTH,
using that manually created fields works:
With the help of aggr() you could even set your listbox selections independent of each other...
Does anybody know why auto calendar fields don't work within expression at all?!? --> Bug or feature?!?
Test.qvf 192.0 K
Hello Robin! Thank you very much for your response and effort!
I also first tried using the auto calendar fields in the set analysis but it didn't work. Unfortunately, your solution would not be optimal for me. The example I provided is a simplified version of a bigger data model that includes other tables/fields as well.
1) I would prefer not to change this model. In your qvf file the tables are concatenated into one big table.
2) It is really important that all date fields can be used simultaneously. Some of the KPIs are calculated on a monthly basis, others on a quarterly. For example, I need to select Jan in [A_RequestDate], but Jan, Feb, Mar in [B_OrderDate] and then something else [C_OrderDate]. The pivot table should reflect all this selections accordingly.
3) As some of the KPIs use the date fields in the calculation, I think that it would be best if the selection filters are linked to the calendar (and not created separately as YearMonth in the script)
I hope that this problem has a solution and there is no bug in QS.
so you like to keep your 3 fact tables separately?
Then I think you'll need a link table + 3 separate manually created master calendars.
I think the easiest way solving this would be using alternate states. So you could keep your structure and the date selections wouldn't influence the other dates that have no corresponding group.
But I don't know if alternate states are available in Sense yet...?!?
Hi Robin and thanks for your comment! I do not think that this functionality is available in QS by default. Maybe with some extensions, but this is unfortunately not an option in our company as we had some bad experience in the past with extensions that stopped working after QS update.
If anybody could suggest something or explain why the set analysis is not working it would be great! Thank you!
... basically you could use 3 separate manually created master calendars so you don't need to put the monthyear field into your existing tables... so far so good...
The set analysis would also work then.
The problems are the "listboxes"...
I tried something like
>} YEARMONTH_FROM_MASTERCALENDAR_A), YEARMONTH_FROM_MASTERCALENDAR_A)
But this didn't also work....
Due to everything is connected via the group table I thought alternate states would be (and are!) the appropriate solution... solved in QlikView within 5 minutes... no joke ...
So at the moment I'm out of ideas.
Perhaps you could build a link table that contains every combination of every selection of the 3 datefields ... but I don't have that much time at the moment...