Double counting sum in chart when columns are missing

anothergreg — Wed, 25 Nov 2020 16:16:04 GMT

I have two datasources; an employee census file (EEID, Title, Start Date, End Date) and an activity list (EEID, Activity Description, Activity Quantity, Activity Complete Date).

I have logic that checks an activity to apply it to an employee and their title while they are employed, and sums the quantity of the activity logged. Logic is below.

=if((((Start Date)<=(Activity Complete Date))and((End Date)>=(Activity Complete Date))),sum(Activity Quantity))

My Straight Table Chart contains information grouped by other columns available in the data (Cities, for example) and it will not work unless I include Activity Complete Date in the Chart. If I remove it, I get multiple entries of Activity Quantity. If I have Title included (which is a 1x1 relationship with Start Date and End Date, showing a person's position in the company at a given point in time) I will have a duplicate row with the same Activity Quantity count even though the Start Date and End Date are completely outside of the Activity Complete Date.

Yet as soon as I include Activity Complete Date in the Chart, it corrects itself and I get an accurate count. What's happening here to cause this issue?

topic Double counting sum in chart when columns are missing in QlikView

Double counting sum in chart when columns are missing