Fake Week and Sum with Month
Hi, want to SUM value with Month.
I set the time setting as "Week General".
It has no month aggregation, so I made the Month List.
And I calculated month value as a described below.
<Solution1>
Formula:
TimeSum(Weekvalue, start, end)
＋value(adj)[Lookup:start-1]
- value(adj)[Lookup:end]
| week(start/end) | start | end | |||||||||||||||
| week | w6/28 | w7/5 | w7/26 | ||||||||||||||
| day | 6/28 | 6/29 | 6/30 | 7/1 | 7/2 | 7/3 | 7/4 | 7/5 | ・・・ | 7/26 | 7/27 | 7/28 | 7/29 | 7/30 | 7/31 | 8/1 | |
| value | 200 | 0 | 0 | 100 | 0 | 100 | 0 | 100 | 0 | 0 | 0 | 0 | 300 | 0 | 200 | ||
| value(adj) | 0 | 0 | 0 | 100 | 0 | 100 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 200 | ||
| adj | ☑ | ☑ | ☑ | ☑ | ☑ |
I read this article.
But I want to Sum month value more simpler.
I think I should make fake week, Like this.
| Month | Week | Start | End | Start(adj) | End(adj) |
| Jun-21 | W/c 28 Jun 20 | 6/28 | 7/4 | 6/28 | 6/30 |
| Jul-21 | W/c 5 Jul 21 | 7/5 | 7/11 | 7/1 | 7/11 |
| W/c 12 Jul 21 | 7/12 | 7/19 | 7/12 | 7/19 | |
| W/c 26 Jul 21 | 7/26 | 8/2 | 7/26 | 7/31 |
I have to adjust the start and day Properties.
And I think can use "Timesum" value with "start(adj)" and "end(adj)".
But I couldn't do this because of the restriction of "Timesum" function.
Do you have any solution?
I read this article, but the start/end date of week was same between "Real Week" and "Fake Week".
https://community.anaplan.com/t5/How-To/Modeling-Video-Series-1-Flexible-Time-Mapping/ta-p/93234
Comments
-
I am concerned about when using another function.
If I want to calculate the average of value in the month,
I can not use this below formula.
: Timesum(value, start, end ,average)
I have to use this formula.
(Timesum(value, start, end,Sum) + value(adj)[Lookup:start-1] - value(adj)[Lookup:end]) / days()
I am concerned about I have to be careful about formula,
and there are another restriction like this.
0 -
Quick question, why did you set the model Time granularity to Week General when you can use the other options to rollup to Month?
Rob
0 -
@rob_marshall
Because of saving memory, I use Week:General.
Another Weekly Time setting has 53weeks at least.
0 -
True, it is a little more space, but at the end of the day, I think it will save you more than using the 13 Week General Model. And, it will actually perform better because you will not have to do all of the extra mappings for Months, Quarters, Year, etc.
Rob
0
