Set Analysis by filter in other fields

[Amit_B]

Hi,

I will explain about my problem and what I tried to do.
I asked it before but the solutions I received for the example didn't help me with the complete problem.

I have 3 main tables in my data model (House Rental company):
* Facts - includes orders and treatments (less than 100 lines per day)
* Calendar - connects to Facts by Date field.
* Pool - include the status of every apartment in every date (about 2K lines per day) - connects to Facts by key field (Date & ApartmentID).

The problem is that the results for the measures are incorrect when I filtered based on a date field (Year, Month, etc.), I don't get the 'status' for the entire pool in the selected date, but only for what is linked to the Facts table.
I can connect the Pool table to the Calendar by Date field, but a synthetic key is created.
So I tried creating additional date fields in the Pool - Year2, Month2, etc. By using the new fields my calculations are correct, but i need the data will filtered by the main calendar.

Let's say for example that I count the number of apartments in certain status.
I tried to fix this in 2 ways:
1. Using P() - error in expression:
Count({<Year2=P(Year), Quarter2=P(QuarterID) Month2=P(MonthID), Date2=P(Date), YTD2=P(YTD), MTD2=P(MTD), StatusCode={1}>}ApartCode)
2. I create variable for the selection of each field - the result is 0 or null.
Count({<Year2={"$(=vSelectedYear)"}, Month2={"$(=vSelectedMonth)"}, ...>}ApartCode)

I would appreciate help with defining the formula.

Thanks.

[Dataintellinalytics]

Please share sample /mock dataset

[Rohan]

Hi Amit,

Just unlink the Calendar from your data model, that way the date selections can be passed as just a range within the set instead of applying to the whole data. This concept is called as Island/Unlink Calendar.

Check out the following link for more details :

https://cassio-bolba.medium.com/date-island-qlik-sense-25ea793aca2d

Regards,

Rohan.

[marcus_sommer]

You couldn't enforce a data-association per set analysis which isn't supported by the data-model. This means you need to adjust the data-model in some way.

I think I would simply merge Facts and Pool by joining them (if there is only one record per date and apartment) or otherwise concatenating them to avoid a duplication of records.

Now each apartment has for each date a record and the calendar-connection will work.