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-Gregorian/ta-p/59839

 

 

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

 

 

 

Answers

  • 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.

  • @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

  • @rob_marshall
    Because of saving memory, I use Week:General.
    Another Weekly Time setting has 53weeks at least.
  • @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