Fake Week and Sum with Month

hyudolee
Occasional Contributor

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       
weekw6/28     w7/5 w7/26      
day6/286/296/307/17/27/37/47/5・・・7/267/277/287/297/307/318/1 
value2000010001000100 00003000200 
value(adj)000100010000 000000200 
adj            

 

I read this article.

https://community.anaplan.com/t5/How-To/Convert-Values-from-a-445-Calendar-to-a-Normal-Monthly-Grego...

 

 

But I want to Sum month value more simpler.

I think I should make fake week, Like this.

 

MonthWeekStartEndStart(adj)End(adj)
Jun-21W/c  28 Jun 206/287/46/286/30
Jul-21W/c 5 Jul 217/57/117/17/11
W/c 12 Jul 217/127/197/127/19
     
W/c 26 Jul 217/268/27/267/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

 

 

 

4 REPLIES 4
hyudolee
Occasional Contributor

Re: Fake Week and Sum with Month

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.

rob_marshall
Moderator

Re: Fake Week and Sum with Month

@hyudolee 

 

Quick question, why did you set the model Time granularity to Week General when you can use the other options to rollup to Month?

 

2021-09-13_14-08-31.png

 

Rob

hyudolee
Occasional Contributor

Re: Fake Week and Sum with Month

@rob_marshall
Because of saving memory, I use Week:General.
Another Weekly Time setting has 53weeks at least.
rob_marshall
Moderator

Re: Fake Week and Sum with Month

@hyudolee 

 

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