I'm having a problem with a flag on Webi, I have a universe with stores, product sold and date of sale, another one with store and date in which an specific enhancement was received on the store for one or more products. The enhancement date started this year and the stores started selling some time prior to that, each enhacement happened on different products by store and did not happen on the same day.
| Store | Sales Date | Product |
|---|---|---|
| 1 | Jan 1 | Apple |
| 1 | Dec 20 | Soap |
| Store | Enhancement Date | Product |
|---|---|---|
| 1 | Dec 1 | Apple |
| 2 | Feb 1 | Soap |
what I'm hoping to get is a flag that indicate sales on each store before and after the enhancement was done similar to the table below
| Store | Pre/Post | Sales |
|---|---|---|
| 1 | Pre | 40 |
| 1 | Post | 10 |
| 2 | Pre | 100 |
| 3 | Pre | 1 |
| 3 | Post | 10 |
| 4 | Post | 100 |
What I tried to do was to merge the tables by store and create details with all the variables from each universe, however I keep getting Multivalue errors,
| Store | Pre/Post | Sales |
|---|---|---|
| 1 | Pre | 30 |
| 1 | Post | 5 |
| 1 | Multivalue | 15 |
I also tried creating a variable to identify pre/post but I think I may be thinking it wrong
=If(SalesDate>EnhancementDate And Store=Store And Product=Product;Count(Product);0)
Can anyone point me in the right direction, please? Thank you