This article describes how to add a Week Attribute into the Module so that you can expand weeks. In this example, we will use Dynamics GP and Solver Cloud.
To make this attribute you must add it in the SQL View for the for the ERP. The view must have a date that relates directly to the Week calculation that you are doing.
CASE PERIODID WHEN 0 then DATEPART(week,DATEADD(dd,1,TRXDATE+1)) ELSE DATEPART(week,DATEADD(dd,1,TRXDATE)) end as Week
The key part of this is the DATEPART. This formula converts the field TRXDATE to a week number.
Once this is done you can make a week field and import the data into the Data Warehouse.
This will now allow you to do an expansion of week in your Column.
