I have the following dataset:
ENTITIES:
Load * Inline [
eid, eid_org, eid_prod, type, name
1, 1, 1, rep, Fred
2, 2, 2, rep, Wilmer
3, 3, 3, rep, Foo
4, 4, 4, dep, OrgA
5, 5, 5, dep, OrgB
];
ORG:
Load * Inline [
eid_org, org
1, OrgA
2, OrgB
3, OrgB
4, OrgA
5, OrgB
];
PRODS:
Load * Inline [
eid_prod, prod
1, Coke
2, Coke
2, Pepsi
3, Coke
];
SALES:
Load * Inline [
month, eid, prod_sales, qty
1, 1, Coke, 1
2, 1, Coke, 1
1, 2, Coke, 1
2, 2, Coke, 1
1, 2, Pepsi, 1
2, 2, Pepsi, 1
1, 3, Coke, 1
2, 3, Coke, 1
];
I want to aggregate sales on both Org and Product level. The result has to be something like this:
1 column I'm displaying Reps. This works.
2 is the sum of the Qty of their sales. This works.
3 is to which dept / org they belong. This works.
4 is how much that dept / org sold as a whole. This works.
5 is supposed to be how much that org sold, but ONLY of the products Rep is assigned to. As you can see it shows wrong now. Wilmer should have 6, the rest is correct.
I tried a lot of things and this is as close as I could get.