Forum Discussion
Last Year To Date Using ISO Calendar
Hi, We work to an ISO Retail Week Calendar, where RetailDayofYear compares with the corresponding RetailDayofYear of last year. This differs during a 53 week year (2016,2020) when Week 53 will co...
If i was to replicate in SQL what i am trying to achieve it would be, i would then need to apply the logic for TD to the daily totals obtained.
select [Transaction Date], dd.RetailDayofYearLY, dd.RetailYear, SUM([Sales Value £]) as TotalTY, LY.TotalLY from SALESFACT f
left join DimDate1 dd on f.[Transaction Date]=dd.DateKey
left join (select retailyear, retaildayofyear, sum([Sales value £]) as TotalLY from V_RetailSalesFact f
left join DimDate1 dd on f.[Transaction Date]=dd.DateKey
group by retailyear, retaildayofyear) LY on (dd.retailyearly = LY.RetailYear and DD.RetailDayofYearLY=ly.RetailDayofYear)
group by [Transaction Date], LY.TotalLY, dd.RetailDayofYearLY, dd.RetailYear