Set analysis in pivot table

[steve_br]

Hello!

I have an app with data coming from three tables - A, B, C. They are used to calculate various KPIs. Each table has its own date field and all three are linked to another table showing group names. The data model looks like this:

The requirement is to show the KPIs per group. To do this I created a pivot table but since tables A, B, and C are now linked through the Groups table, all values in the pivot table update after I select a date field. I tried to fix this with set analysis, but it does not seem to work. Below an example.

This is my initial situation with the KPIs, the corresponding date fields, and the pivot table which I would like to have:

I want the values in the first column to be updated only when I select a month in A_RequestDate. However, this does not work, even though it is a requirement in the set analysis:

The same rules should also apply to the rest of the columns, i.e. B_OrderDate should not change columns 1 and 3, etc.

I also attach a QVF with sample data. Hope anybody could help!

Best,

Steve

[Anonymous]

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?!?

[steve_br]

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.

[Anonymous]

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...?!?

[steve_br]

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!

[Anonymous]

... 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

=aggr(only({1<YEARMONTH_FROM_MASTERCALENDAR_B=, YEARMONTH_FROM_MASTERCALENDAR_C=

>} 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...

Regards Robin

[steve_br]

Thank you, Robin, for your time and effort! It is much appreciated!!

I will continue trying to make this work. In the meantime, if anybody can suggest something, I would be very happy.

Cheers,

Steve