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.
We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question
@Isabel_Navarro, period over period are table calculations, when you add a new column of addDatetime, it basically adds a new dimension partition to the result, now you are essentially comparing YoY for each addDatetime() unique value, hence it is not passing any results and will results in blank/NULL value. So you might need to remove that additional last year date column.
total is not supported for period over period table calculations, I will flag this as a feature request.
