Alteryx Designer Discussions

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

General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!

This Year/Last Year Sales with Leap Year

jmarleigh
7 - Meteor

Hi all,

 

I'm having an issue with one of my Alteryx workflows that I need help with. Currently I have a workflow that uses a dynamic input to figure out the current fiscal week, and then go and get that data from HANA, in conjunction with that it also finds the historical fiscal week from one year ago and joins them together. The issue with doing this is it doesn't account for leap year at the moment so I was hoping someone could help me out. I've created a fiscal calendar and can now pull back the historical data from 52 weeks ago using another string replace in the dynamic input.

 

The spot that is problematic, however,  is in this in the join here:

 

TY.YFISCWEEK = (LY.YFISCWEEK + 100)

 

If I change LY.FISCWEEK to be 99 instead of 100 that will work in most instances, however, that doesn't work in fiscal week 01.

 

Below is also the full SQL

 

Select Coalesce(TY.PLANT, LY.PLANT) As PLANT, Coalesce(TY.STAT, LY.STAT) As ARTICLESTATUS, Coalesce(TY.YFISCWEEK, LY.YFISCWEEK + 100) As FISCWEEK, Coalesce(TY.FISCYEAR, LY.FISCYEAR + 1) As FISCYEAR, Coalesce(TY.MATERIAL, LY.MATERIAL) As MATERIAL, Coalesce(TY."Sum_Sales_Amt", 0) As "Sum_Sales_Amt", Coalesce(TY."Sum_Sales_Qty", 0) As "Sum_Sales_Qty", Coalesce(TY."Sum_Sales_Cost_Amt", 0) As "Sum_Sales_Cost_Amt", Coalesce(LY."Sum_Sales_Amt", 0) As "LY_Sum_Sales_Amt", Coalesce(LY."Sum_Sales_Qty", 0) As "LY_Sum_Sales_Qty", Coalesce(LY."Sum_Sales_Cost_Amt", 0) As "LY_Sum_Sales_Cost_Amt" From (Select _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".PLANT, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".MAT_PLANT_YARTSTAT As STAT, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".FISCYEAR, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".YFISCWEEK, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".MATERIAL, Sum(_SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1"."Sales_Amt") As "Sum_Sales_Amt", Sum(_SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1"."Sales_Qty") As "Sum_Sales_Qty", Sum(_SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1"."Sales_Cost_Amt") As "Sum_Sales_Cost_Amt" From _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1" Where _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".YFISCWEEK In (54321) And _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".MATERIAL In ('ARTLIST') Group By _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".PLANT, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".MAT_PLANT_YARTSTAT, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".FISCYEAR, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".YFISCWEEK, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".MATERIAL) TY Full Outer Join (Select _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".PLANT, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".MAT_PLANT_YARTSTAT As STAT, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".FISCYEAR, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".YFISCWEEK, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".MATERIAL, Sum(_SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1"."Sales_Amt") As "Sum_Sales_Amt", Sum(_SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1"."Sales_Qty") As "Sum_Sales_Qty", Sum(_SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1"."Sales_Cost_Amt") As "Sum_Sales_Cost_Amt" From _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1" Where _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".MATERIAL In ('ARTLIST') And _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".YFISCWEEK + 100 In (54321) Group By _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".PLANT, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".MAT_PLANT_YARTSTAT, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".FISCYEAR, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".YFISCWEEK, _SYS_BIC."bw2hana.POS/CAV_ERP_POS_CONSOLIDATED_CV1".MATERIAL) LY On TY.PLANT = LY.PLANT And TY.MATERIAL = LY.MATERIAL And TY.YFISCWEEK = (LY.YFISCWEEK + 100) And TY.STAT = LY.STAT

 

 

steven-barsalou
8 - Asteroid

Not a direction solution, but hopefully helpful. 

 

I generally find week numbers meaningless, because most people don't know what week 37 is without consulting a calendar to figure out that is 9/16/2019.  I prefer to use the Monday of the week to represent that week.  Use this formula and replace "your_date" with whatever your date field is to derive the Monday of that week.  Do not change the 1900-01-01, that was a Monday and the math is based on that being true.

Cast(your_date - ((your_date - Cast('1900-01-01' As Date)) Mod 7 ) As Date) As mon_of_your_week

 

Also note that your_date - 364 days will always yield a day in the previous year on the same day of week, works with Leap years too.

 

Now that you have the Monday of your week, subtract 364 days from that date, to find the week you want to compare it against.  

 

To implement this, you'll likely need to map those dates to fiscal weeks, but your data source should have a calendar that maps dates to fiscal weeks.

 

 

Labels