Hello,
I'm having a question about set analysis. The situation is pretty complex so I'll try to explain it as best as I can (and reduce simplicity by only mentioning relevant stuff).
Imagine a database table representing product sales & purchases with these fields:
transaction_id, datetime, productcode, shop, sale_purchase, amount
Each time a transaction is done, a row is inserted in the table with productcode, shop, S or P in the sale_purchase field, and amount.
transaction_id is the primary key so when two customers buy the exact same product in the same shop a new row is inserted.
I want a pivot table where I can see sales per product per shop.
So I have a table with first dimension productcode, and second dimension shop.
I used as expression sum(amount).
Now I have the amount of sales/purchases per shop.
However, I want to see every shop for every product. Even if the sales are zero. If a certain product hasn't been bought/sold in a certain shop, no row exists in this transaction table so the pivot table doesn't show that either.
I don't know if there is an easier way to solve this but I did it by modifying the load statement in the script editor:
1) outer joining every distinct product code with every distinct shop from the transaction table.
2) left joining that with the transaction table itself.
This way I successfully had an overview of every shop for every product, even if the sum of sales & purchases is zero (the rows exist now because of the joining).
Of course this is not a useful pivot table, we also want to split sales and purchases. So I instead of sum(amount) I made two expressions for the pivot table:
- Sum({<sale_purchase={"S"}>} amount) = total sales
- Sum({<sale_purchase={"P"}>} amount) = total purchases
One problem, because these are "custom filters", all product/shop combinations that don't have a certain product bought and sold are filtered out. Those rows disappear from the pivot table. I want to see the result as zero again in those cases.
I solved this yet again by the same "cheat" as above: also outer joining with every distinct sale_purchase value (S and P) before I left join with the transaction table itself. That way I get my desired result but isn't there a better way? I keep multiplying my database rows that need to get loaded, and now I also want to use set modifiers to filter by specific transation dates (which I don't know beforehand. For example: all sales before july) so I can't keep doing outer joins (or I'd need an infinite amount of calculated fields like "before_july" etc...).
Any ideas? Another way I can keep all rows in my pivot table instead of outer joins? I hope my explanation is a bit clear.
