I have a set of time series data for exports. I want to create a line chart which shows year on year % change as a time series. This will ideally be % change on a daily basis. So the timeseries will show today vs 1 year ago, then yesterday vs a year ago yesterday and so on as a time series so we can see how the YoY% changes over time. I guess I need to use some sort of set analysis. The formula will be:
(Exports / Exports 1 year ago) - 1
The set analysis I am using for exports 1 year ago is:
This gives me one datapoint for one year ago from my latest datapoint which even then doesn't look right. I have created day and week indices. I would be happy with year on year on a weekly basis on a daily basis does not work.
Set analysis calculates on set per chart, not a set per row. What you need to do is change the data model so each date is linked with the date a year earlier. This can be done with an AsOf table. See this document for some examples: Calculating rolling n-period totals, averages or other aggregations. As I said in your case you want to relate each date with the same date a year earlier.
You might want to use the addyears function instead of subtracting 365 since that can trip you up with leap years.
