LinkedIn
Twitter
Hello, I have 2 tables.
Sales
| Date | Product | Size | Number Sold |
|---|---|---|---|
| 1/1/2021 | T-shirt | L | 500 |
| 1/1/2021 | T-shirt | S | 400 |
| 1/1/2021 | Pants | L | 300 |
| 1/2/2021 | T-shirt | L | 600 |
| ½/2021 | T-shirt | S | 300 |
| 1/3/2021 | T-shirt | S | 50 |
Return Reasons
| Date | Product | Size | Main Reason | Number Returned |
|---|---|---|---|---|
| 1/1/2021 | T-shirt | L | Bad Fit | 50 |
| 1/1/2021 | T-shirt | L | Wrong Color | 25 |
| 1/1/2021 | T-shirt | S | Don’t like style | 30 |
| 1/3/2021 | T-shirt | S | Bad Fit | 30 |
| 1/3/2021 | T-shirt | S | Don’t like style | 10 |
I want to display these in a Looker Table as
Date Range Filter 1/1/2021-1/3/2021
| Product | Size | Main Reason | Number Returned | Number Sold | Return Percentage |
|---|---|---|---|---|---|
| T-Shirt | L | Bad Fit | 50 | 1100 | 4.55% |
| T-shirt | L | Wrong Color | 25 | 1100 | 2.27% |
| T-shirt | S | Don’t like style | 40 | 750 | 5.33% |
| T-shirt | S | Bad Fit | 30 | 750 | 4% |
Notice that the number sold by size and product is constant by main reason. I currently have the pk on Sales as CONCAT(date, product, size) and the pk on return reasons set as CONCAT(date, product, size, main_reason). The concatenation is happening in SQL on a derived table, not in the LookML. I have these two tables joined on date, product, and size, one to many. (I have tried a lot of other configurations but this is the most logical to me.
I think this would be straight-forward if I set each main reason as a column however, the reasons fluctuate over time which would leave me constantly updating the lookml to add new measures.
Inside of looker, there is some strange behavior where the number sold by day is changing, some inconsistency with the join. I can’t figure it out. Any ideas?
