Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Is it possible to get the sum or average of previous columns and display it in the current and future columns. Basically, the columns are number of weeks of the year.
Cust | Category | W47 | W48 | W49 | W50 | W51 | W52 | W53 |
A | TotalHours | 40 | 20 | 20 | 40 | 40 | 40 | 40 |
A | AvgHours | 30 | 40 | 20 | 30 | 30 | 32 | 30.3 |
Currently we are at Week 50 (W50) in the pivot, the value to reflect in W50 of AvgHours Category should be Average hours of W47, W48, and W49 thereby W50 = 30 (in bold and italic).
Same goes with the succeeding weeks. W51 value should be average of of W47, W48, W49, and W50. And so on...
The Total Hours is SUM(Hours) only will work. But the AvgHours is very challenging is it scoped with the week headers.
Do you have an idea?
TIA
you can do that in the expression - not referencing the columns but in your expression you indicate which weeks to average. you may also need to bridge the weeks going multiple weeks back
also how will the average for weeks 47 to 49 be calculated? it appears the average is cumulative. how about weeks before week 47?
Week 47 to 49 shouldn't be shown. The table I created is on for purposes showing it in details.
I tried:
SUM({< [Week] = {"=[Week] > $(vCurrentWeek)} >}Aggr(AVG({< [Week] = {"=[Week] >= $(vStartWeek) AND [Week] < $(vEndWeek)"}>} [Hours]), [Cust],[Week]))
Where: declared as variables look like below:
vCurrentWeek = Week(Today())
vStartWeek = Week(Today()-3)
vEndWeek = [Week]
But it gives me 0.
look at this
it has provision for historical weeks but if you just need current week going forward you just use CUMULATIVE datetype