My promos are String (in the dataset I have a column “PromoName”). The promos are not linked to a particular period of time in my data set. I have another column in my data set Year (e.g. “2022” and "2023). I would like to do a YoY comparison of the spent and CPC between the current year and the previous year. So add a calculated filed “Spent YoY” and “CPC YoY” that would take all the values of a the current year and compared them to the previous year.
However, because I don’t have dates I haven’t found a way to use the function “periodOverPeriodDifference”. Is there a workaround for when there is not dates in the dataset?
Hi @ErikG ,
I’m not sure I understand what you mean.
I don’t have a year column in my table. Could you please clarify what you mean by “do the calculation with your year column”?
Thanks!
Hi @ErikG,
the year column is in my data set that is used to feed the dashboard. The column year is not in the table. Apologies if that wasn’t clear.
Here is a screenshot of part my dataset:
To clarify, my table in my first screenshot is empty because I hid the data for confidentiality reasons. My problem is that I don’t know how to add a YoY comparison for the current and previous year for the two columns “dollar spend” and “Avg CPC”. I would like to add a column “YoY Dollar Spend” after the column “Dollar Spend” and add a column “YoY Avg CPC” after “Avg CPC”. For example, if the top filter “Year=2023” is selected then, in the first row of the table, I would see in the column “YoY Dollar Spend” the YoY difference between the 2023 and the 2022 January campaigns, in row 2 the difference for the 2023 and 2022 Valentine Day campaigns…
When I try to change “Year” from an Integer field to a Date field in my QuickSight database it changes all the values to “1970-01-01”. To try test your recommendation I created a new column in my database “Promo start date” with the exact start date of the promo so for example “2022-04-14”.
I have tried doing:
periodOverPeriodDifference(sum(Dollar Spend),{PromoStartDate}, YEAR, 1)
However, because PromoStartDate is a specific date it compares this specific date to last year’s specific date (which for most of the time doesn’t exist because promos didn’t start at the exact same date).
I’d like to see the YoY for promos (my rows in the below table are promos) not YoY for specific start dates (like I have with the many columns below).
It has been some time since we have heard from you but would still like to help you find a solution. If we do not hear from you in the next 3 business days this topic will be archived.
