You can't reference your new field CC_Date in the same LOAD statement where you are creating it.
You can use the new field in a preceding LOAD
or reference the original field name:
Week(CC_Date) as WeekOnNewField;
Date as 'CC_Date' ,
Week(Date) as WeekOnOriginalField,
Your Date field should show a numeric representation in both cases, to allow the Week() function to work.