The title tells only part of the story. Apologies, but I really had no idea how to title this issue in a few words
I was asked to provide a table that shows the daily value of sales and the value of sales on the same day one year ago.
The way I approached this was by creating a straight table having one dimension (date) and two metrics:
Sales = SUM(Sales)
Sales last year = BELOW(SUM(Sales), 365)
This approach delivers as expected until the point the user starts fiddling with the Date Listbox, which is a necessary part of the deliverable.
Let's assume that the first three rows in the table look as follows:
| Date | Sales | Sales last year |
| 2019.10.14 | 158 $ | 169 $ |
| 2019.10.13 | 174 $ | 165 $ |
| 2019.10.12 | 161 $ | 170 $ |
With the current setup, if the end user selects '2019.10.13' in the Listbox what will happen is that 'Sales last year' will turn to 0 (because the relevant row, that is supposed to be 365 places below simply does not "exist")
| Date | Sales | Sales last year |
| 2019.10.13 | 174 $ | 0 $ |
I tried different approaches with set analysis, e.g. by changing the second metric to:
Sales last year = BELOW(SUM{1} Sales), 365) which allows me to retain the 165 $ value but it also forces all dates to remain visible
| Date | Sales | Sales Last Year |
| 2019.10.14 | 0 | 169 $ |
| 2019.10.13 | 174 $ | 165 $ |
| 2019.10.12 | 0 | 170 $ |
None of the above outlined outcome matches the user experience that I want to deliver to my stakeholders. If a user selects only one date via listbox then the table should be reduced to one row, but both the Sales and Sales Last Year metrics should be populated with the correct data.
One solution that I can think of is to create the final table already at script execution time, but if there is a way to do it in the front-end, I would prefer it by far.
Thank you.
