The reason that A is not appearing in months 2 and 3 is because there is no data for them, it is not null data. One way to get it to appear in the view is to either add it directly to the data or create a lookup table with each month and PeriodCd and join it to the original table.
In terms of the calculation that wants to be created, it should be similar to the following:
[Running sum FTE]/total(countd(Month))
To get the countd function to work, you will need to create and use an extract.
Hope this helps!
Adding data is not possible - my example was quite trivial, but in fact my database is more complex. In rows I have different Domains, and under these domains different entities.
I tried custom SQL but it was calculating for ages.
Finally I managed to crack this myself without lookup tables. For anyone who is interested - I used a custom calculation (computed using MONTH):
RUNNING_SUM(SUM([FTE])) / [Running month number]
where Running month number is calculated as:
IF ATTR([Month]) = LOOKUP(ATTR([Month]),-1) THEN
This means even if there are no FTE's in given month, the formula will still divide the running total by running month number.
Moving average.twbx.zip 66.8 KB