Average totals per month should sum(..) all previous dates until the requested monday's instead of only the month data

[Anonymous]

What we try to do is to get the average week supplies of a month (e.g. august), which are based on the totals of each monday of the month.

These totals are based upon the [ArticlePosts.Remaining number], If we want the totals of august we should count all totals of the remaining number (per articlecode and multiply by the fixed transfer pricing) until the monday.

See partial code below of the tables:

...
Articles:
LOAD ArticleCode,
 [Article.Fixed transfer pricing]
...
FROM ...


ArticlesPosts:
LOAD BookingDate,
 ArticleCode,
 [ArticlePosts.Remaining number],
WeekStart(Date(Num(ArticlesPosts.BookingDate) + 6)) as BookingWeekMonday,
 Date(MonthStart(WeekStart(Date(Num(ArticlesPosts.BookingDate) + 6))), 'MMM-YYYY') AS BookingMonthYearMonday,
 Month(WeekStart(Date(Num(ArticlesPosts.BookingDate) + 6))) AS BookingMonthMonday,
 applymap('Quarter', num(month(WeekStart(Date(Num(ArticlesPosts.BookingDate) + 6))))) AS BookingQuarterMonday,
 Year(WeekStart(Date(Num(ArticlesPosts.BookingDate) + 6))) AS BookingYearMonday
...
FROM ...


PostsCalender:
LOAD
 TempDate AS BookingDate,
 Year(TempDate) AS BookingYear,
 APPLYMAP('Quarter', NUM(Month(TempDate))) AS BookingQuarter,
 Month(TempDate) AS BookingMonth,
 Week(TempDate) AS BookingWeek,
 WeekDay(TempDate) AS BookingWeekDay,
 Day(TempDate) ASBookingDay,
 Date(MonthStart(TempDate), 'MMM-YYYY') AS BookingMonthYear
RESIDENT
 TempCalendar
ORDER BY
 TempDate;
...

The problem is that if we select a BookingMonthYearMonday (the month lets say august) we only get the data of august, but in fact we would like to get the count until that particular date.

August has 5 mondays on which we would like to know the total amounts and we want to get the average of these amounts. The averages is not the problem, but the problem is getting to count the data of [ArticlePosts.Remaining number] until the WeekMonday.

A small sample of the data:

BookingsWeekMonday	Bookingsdate	Remaining number
6/7/2009	6/7/2009	1
6/7/2009	3/7/2009	23
13-7-2009	7/7/2009	1
13-7-2009	8/7/2009	1
13-7-2009	9/7/2009	2
13-7-2009	10/7/2009	2
13-7-2009	13-07-2009	96
20-7-2009	14-07-2009	1
20-7-2009	15-07-2009	1
20-7-2009	20-07-2009	23
20-7-2009	16-07-2009	32
20-7-2009	17-07-2009	161
27-7-2009	24-07-2009	-4
27-7-2009	21-07-2009	3
27-7-2009	27-07-2009	5
27-7-2009	23-07-2009	51
27-7-2009	22-07-2009	7473
3/8/2009	30-07-2009	1
3/8/2009	31-07-2009	1
3/8/2009	1/8/2009	1
3/8/2009	3/8/2009	38
3/8/2009	28-07-2009	66
10/8/2009	7/8/2009	1
10/8/2009	8/8/2009	1
10/8/2009	6/8/2009	2
10/8/2009	5/8/2009	3
10/8/2009	4/8/2009	12
17-8-2009	11/8/2009	1
17-8-2009	14-08-2009	3
17-8-2009	13-08-2009	6
17-8-2009	12/8/2009	7
17-8-2009	17-08-2009	40
24-8-2009	20-08-2009	1
24-8-2009	21-08-2009	1
24-8-2009	18-08-2009	2
24-8-2009	19-08-2009	2
24-8-2009	24-08-2009	16
31-8-2009	26-08-2009	1
31-8-2009	25-08-2009	2
31-8-2009	27-08-2009	2
31-8-2009	31-08-2009	7
31-8-2009	28-08-2009	116

The numbers which we need for august (based on the data sample above) should be:

3/8/2009	7979
10/8/2009	7991
17-8-2009	8031
24-8-2009	8053
31-8-2009	8181

These totals are all totals of the previous dates until the week date.
For these numbers we can calculate the averages of the month.

Has anyone got suggestions?

Thx already!

[#2]

Hi,

see the attached file for an idea.

Good luck!

Rainer

[Anonymous]

The idea is clear, but I don't know how to get the total overview in one table instead of a total row in the table ...

We would like to have one overview of the correct totals (in fact we also have some averages over the weeks but for a start the totals are ok).

Don't know how to get it in one table ..