I have a data set that aggregates sales by promised ship date. By day I am displaying a summation of quantity due, line count, and extended sales. The user wants to compare each to the prior days aggregation
Day Qty Count Sale Yesterday Qty Yesterday Cont Qty Chg Cnt Chg
1 500 20 50000
2 520 21 60000 500 20 20 1
3 450 18 60000 520 21 -50 -2
The data I have is individual lines that I am aggregating by day but I do not see how I can populate the “yesterday” fields. I have tried sum(qty) where (dayofmonth(promisedate) = priorday) - prior day is a variable that excludes weekends - but I am just getting empty values.
Any suggestions?
Thanks