Hi, I have a table similar with the following columns:
City, Sales, Date
Dates include only first day of months from the last 5 years.
I want to end with a table with the following columns.
City, Past 6 months average sales, % of Past 6 months average sales vs same past 6 months of year ago, Past month, Past month -1, Past month -2… and so on.
I imagine I have to calculate the average of the dates and somehow concatenate them below my original table so I would end with something like this.
City | Sales | Date
NY | $100M | 01/01/18
NY | $100M | 01/02/18
NY | $100M | 01/03/18
NY | $100M | P6M
But I don’t know if that’s the structure I require to later pivot either in knime or visualization software like spotfire/tableau/powerBI and I have no clue how to start calculating the averages based on date.
Can anyone help?
Thanks a lot in advance!