topic Re: difference of sales between last 12 weeks current year vs last year in SAS Visual Analytics

difference of sales between last 12 weeks current year vs last year

touwen_k — Sun, 26 Sep 2021 15:12:03 GMT

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.

Re: difference of sales between last 12 weeks current year vs last year

Sam_SAS — Mon, 27 Sep 2021 15:08:52 GMT

Hello,

You can use the ParallelPeriod() operator to compare quarterly data.

https://documentation.sas.com/doc/en/vacdc/v_003/vareportdata/p0dovqqp325peyn1dpijpxxmwj83.htm#p1dn6xullue9hkn131dkuudpz2br

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.

Sam

Re: difference of sales between last 12 weeks current year vs last year

touwen_k — Mon, 27 Sep 2021 15:41:59 GMT

hello Sam,
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

Re: difference of sales between last 12 weeks current year vs last year

Sam_SAS — Mon, 27 Sep 2021 16:01:08 GMT

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:

https://documentation.sas.com/doc/en/vacdc/v_003/vareportdata/p1vsphor4qnevcn13p4bepmj5wwx.htm

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! 🙂

Sam