1 Reply Latest reply: Sep 8, 2011 9:26 AM by dtcqlikleren RSS

    Set analysis: prevent filtering of rows in pivot table



      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.