I donot know how to solve it with sql. But why not doing it using qlikview functionality?
I would use a line chart with the dimension: "if (num(today()-num(date_in) < 27), weekday(date_in))" -> only tale the last 28 days
and the expression: avg(raw_value).
Set Suppress When Value is Null and Sort by Weekday(date_in)
And you see a wonderful linechart with the weekdays from Sunday to Saturday and the average of the 4 values of each weekday. You can use a variable instead of today() so you have a line chart depending from a selected date.
Is that what you need?