We have a a table of various daily sales grouped by date as shown in the image in QuickSight. The first column is the day of the week, the second is the date, the third is last years date by using the formula: addDate(-52,‘WK’,date), the fourth is the sum of sales per day (sum(sales - discounts)) and the fifth is the equivalent last year sales by using the formula: periodOverPeriodLastValue(sum(sales - discounts),date, WEEK, 52). We have two issues:
First, whenever we want to use last years date as a dimension in the table, the periodOverPeriod function turns blank, that is why is has been located as a value and why there is a total at the bottom. Is there a way to avoid that?
Second, when adding the total at the bottom, the periodOverPeriod function has a blank total since it is a custom aggregation. We want to be able to visualize the total of last years sales (Ventas (LY) column) as well.
Thanks in advance.