Set analysis: prevent filtering of rows in pivot table

[#1]

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.

[#2 · Accepted solution]

I think I answered my own question. Using if instead of set analysis.

Sum(if(sale_purchase='S', amount))