Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Year Over Year Day of week adjustment in reporting

Highlighted
8 - Asteroid
 

I am trying to resolve an issue involving comparing Year Over Year data results by day, and cant seem to quite figure it out.

I want to be able to accurately compare Year Over Year results by a specific day - Historically we have been calculating this as [Years Back] * 364 to obtain the number of days between the two dates, but as the yearly difference becomes greater than 7 it becomes incorrectly calculated.

 

I attached a picture here showing the anomaly where there is not a constant difference YoY and between two dates. 2015 is not a leap year.

 

 

 

What is the best way to calculate any given dates past equivalent date? If i have two calendar tables, what do I match on? (I can get any of the following fields to match Date, Day of Week Number, Day of Month Nomber, Week Number of Month, Week number of Calendar Year etc.) For example, need to input date, and have an output of past year equivalent Like So:

Below scenario is for labor day
Given Date | Past Year equivalent 20190902 | 20180903 20190902 | 20170904 20190902 | 20160905 20190902 | 20150907 20190902 | 20140901
Highlighted
12 - Quasar

@mkav9893  it seems like you're mainly worried about holidays, which can move depending on the exact holiday. For example:

 

Martin Luther King day - 3rd monday in January

President's Day - 3rd Monday in February

Memorial - Last Monday in May

 

 

If you can find a list of these holidays (it's pretty available) then you can do the work within the dates to flag each of the holidays for each year.

Highlighted
8 - Asteroid

Holidays are the main concern, but I want to be able to pick any day of the year and have that date calculated as well in comparison. 

Highlighted
ACE Emeritus
ACE Emeritus

In addition to the holiday issue mentioned by @neilgallen, it's quite possible that your math of -364 is not flexible enough to accommodate leap years. Perhaps the solution is to strip the year off of the date field, update just the year with the year desired, then reassemble the date field. Using Feb 29 as an example (because it's impossible) if your date is 20160229, you would split it into two numeric fields - 2016 and 0229. Do the math on the 2016 part only, then combine the two fields again and you'll have the same month and day regardless of the year. But I agree with @neilgallen - this will work fine for most cases, but if you need to know what days were holidays, you may want to join some secondary data source for that.

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
Highlighted
12 - Quasar

@mkav9893  There are other questions here, mainly around what you mean when you want to compare year over year, excluding holidays.

 

Are you wanting to compare 2019-02-18 vs 2018-02-18, or do you want to compare the monday that 2019-02-18 (the 7th monday of the year) was vs the same day last year?

 

I'm also assuming that Julian dates (1-365/366) won't work?

Highlighted
5 - Atom

I am also struggling with year on year comparisons in trying to match the same day last year to this year but having issues around leap years. 

 

@neilgallen is the calculation you mentioned meant to be used as is or is there a technique to using it?

 

Any help greatly appreciated!

 

thanks

Highlighted
12 - Quasar

Hello @mkav9893,

 

You may want to check out this post: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Holiday-and-Date-Data-Set-Builder/m-p/... 

 

This calendar that the workflow creates would allow you to join to previous years based on day of week and what week of the year you are in. Hopefully this can meet your needs.

Highlighted
12 - Quasar

@annap I wasn't referencing something specific, just asking questions with regards to year-over-year calculations. There are a few details that are needed when someone usually says that.

 

"Do you mean same exact day from previous year? same day of week (and week number) vs previous year, etc?"

 

The post mentioned earlier with the attached workflow is a great example of what's required sometimes to do that type of calculation.

 

Happy to help in any way possible. Best way is to attach whatever data / workflow you're working on if you can.

Labels