I have loaded data from 14th July 2021 to 24th November 2022 into Qlik Sense. I have created a table to show daily data that uses a filter pane to allow the user to choose a certain week.
For example, the user has chosen week beginning 7th November.
|
Total Jobs |
Completed |
Completed Rate |
07-Nov-22 |
778 |
678 |
87% |
08-Nov-22 |
801 |
750 |
94% |
09-Nov-22 |
500 |
400 |
80% |
10-Nov-22 |
450 |
389 |
86% |
11-Nov-22 |
652 |
501 |
77% |
12-Nov-22 |
101 |
89 |
88% |
13-Nov-22 |
28 |
27 |
96% |
I’ve added a line chart showing the completed rate (Completed/Jobs) which is a master item. I wish to get the average completed rate for the previous 4 weeks shown in the chart. In this example it would cover 10 October to 6 Nov or week beginning 10 Oct 2022 to week beginning 31 Oct 2022
Should I choose week beginning 10 Jan, the 4 week average would cover 13 Dec 2021 through to 9 Jan 2022 or week beginning 13 Dec to week beginning 3 Jan 2022.
I have tried RangeAvg(Above(Avg({<WeekStart(PlannedDate)>}[Completed Rate]),0,4))*Avg(1), but it doesn’t work.