This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
My question is quite simple, but I have not been able to find the answer in the discussion. I have sales data from the beginning of 2017 up until today's date and I would like to split it into TY and LY. I am trying to eliminate all the dates from 2017 and to be left with 4 columns: Date - starting from 01/01/2018; Department; Sales TY - sales per day this year; Sales LY - the daily sales from 2017 displayed next to the corresponding daily sales from TY. I am trying to do this as it will be easier to later analyse the data in Tableau. I have found this task a bit tricky as some departments aren't reported each day in both years and some of them have closed (not reported anymore OR still being reported as having 0 sales).
The file titled "example" is a piece of the raw data. "Solution" is how I would like the data to look like, where Sales TY are the actual sales on the specified date and Sales LY are the sales that happened exactly one year ago on that day (in the same departments and brand ID). My main problem is that when blending Alteryx excludes the data for the departments that are not reported anymore/don't have a corresponding record for TY or LY.
My workflow looks like this (I have selected the place where I attempt to split the data and fail :D):
It does work for the most part, but it still excludes some of the departments that are not being reported anymore. I am aiming to still have them and if there is no corresponding record for them in TY or LY to create a cell with "0" in that column.
Example: if Dept 97 was not been reported in 2018 I would like to have its record in LY and to have "0" in the same row in TY.