Updated: Loading all existing information too when using bridge table

[megasupermoon]

I am making a report where the user can see the number of patient falls (HandicapEvents) that occurred in different departments by year, month, day of the week and work shifts. Except for one bar graph, everything else works as intended, and there are no errors in the numbers or other information. The graph shows how many events there are in relation to all treatment days in the unit in relation to 1000 treatment days.

Sum(HANDICAP_AMOUNT) * Sum(HANDICAPEVENT_DURATION) * 1000

However, the numbers are wrong, because in treatment days, the code only takes treatment days related to events. According to my research, when querying the table CarePeriods alone, it gives the correct number of days per unit, but the BridgeTable supposedly messes it up?

How would I get all the existing treatment days for each unit for the graph without messing anything up and breaking Bridge? With new load script (without breaking/deleting brigde), maybe, or something?

I have tried a similar solution as advised in the accepted answer here: https://community.qlik.com/t5/New-to-Qlik-Sense/Complex-Canonical-date-case/td-p/59775,

CareperiodEvents:
LOAD DISTINCT
DEPARTMENT_ID,
CAREPERIOD_DURATION as TREATMENT_DAYS 

RESIDENT
    CarePeriods;

tmpEventDurations:
LOAD
     CAREPERIOD_CURATION as TREATMENT_DAYS

RESIDENT CarePeriods;

LEFT JOIN (CareperiodEvents)

LOAD * RESIDENT tmpEventDurations;
DROP TABLE tmpEventDurations
;

but durations are something like this: 

Queries and data model (don’t worry about the sync tables) attached.

Thanks for advance!

EDIT: It would be awesome if someone can decode/understand "messy" data model and give advice/help me using that, but I added simplified(?) data model too. 

[Rohan]

Hi,

Can you tell us what it is that you are getting wrong in the bar graph specifically ?

That can help you too, in identifying if the numbers are getting affected by a single factor or something else.

Thanks & Regards,

Rohan.

[megasupermoon]

In theory, bar graph is right and it counts right but values in durations are loaded wrong.

If, for example, DEPARTMENT_ID 101 has ten durations, durations are  0, 1, 2, 3, 4, 5, 6, 7, 8, 9 = 45

If DEPARTMENT_ID 202 has three rows, durations are 0, 1, 2 = 3

etc. 

There shouldn't be so small durations.