dear community users,
I have to calculate a variation% of sales, so difference of (12 wks sales of current year - 12 wks previous year)/ 12wks previous year. How to do that in VA? The visualisation should be one single sum. I can work with hardcoded values if needed.
You can use the ParallelPeriod() operator to compare quarterly data.
Comparing the last 12 weeks versus the same 12 weeks from the previous year will be harder. You can perform calculations on dates by using the TreatAs() operator, so in theory you could create some very complex expressions to retrieve and compare the values. It would be MUCH easier to compare the quarterly data if that would be acceptable.
thank you for advice, yes, I am going to do that per quarter, to make my life easier. If the quarter is still running and I would like to compare the same periods, do you have to choose option to today and build a filter to limit the calculations to current month? One more quesiton: how to compare last wk this year to last wk prevoius year to have one single value in a crosstab?regards Karolina
You can select _ToDate_ as a parameter for ParallelPeriod. There are a lot of parameter fields you need to supply, so check the tooltip for each one to get an explanation of what it does.
Alternatively, I think you can use the quick calculation feature to do this. See Difference from previous parallel period here:
Comparing last week this year versus last week of last year is a little bit trickier, but you can do something like this:
RelativePeriod(_Sum_, 'Expenses'n, _ApplyAllFilters_, 'Date'n, _ByWeek_, -1, _Full_)
ParallelPeriod(_Sum_, 'Expenses'n, _ApplyAllFilters_, TreatAs(_Date_, (Treatas(_Number_, 'Date'n) - 7), _ByWeek_, _ByYear_, -1, _Full_)
I didn't have time to test this, but the basic idea should work. You are using TreatAs() to perform math on the date to get the previous week's date for ParallelPeriod.
Let us know if you have any trouble or further questions! 🙂
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.