SUM with Week general value.

hyudolee
Occasional Contributor

SUM with Week general value.

We're using "Week General" as Time Setting.

 

Week General has day and week value, but does not have Month Value.

So I made Month List(Jun21-Dec21), and I want to aggregate the value from day to month.

 

I used "Timesum" function, and could aggregate it week to month.

 Formula:Timesum(Lineitem, Startweek, Endweek)

But I could not aggregate it day to month.

 Formula:Timesum(Lineitem, Startday, Endday)

 

What is this difference?

And when I use week to month aggregation style, how can I adjust the exception day(which is start/end of the month)?

 

hyudolee_0-1631265847396.png

 

2 REPLIES 2
Misbah
Moderator

Re: SUM with Week general value.

@hyudolee 

 

You can not use SUM function on the module which has Time as a dimension. For those we have MOVINGSUM and TIMESUM.

 

In your case since you are using Weeks-General option you will have to create a Fake Timescale.

  1. Week List
  2. Month List

You can have this as a hierarchy setup or you can also map it one time

 

Once done you can use LOOKUP options to pull the values from the module which is dimensioned by Native Time to a module which is dimensioned by Custom Time. But please keep one thing in mind there is no way you can map Custom Day to Native Time Day because there is no option in time Period formatting. So these all numbers will be aggregated at the week level. 

 

Hope that helps

Misbah

hyudolee
Occasional Contributor

Re: SUM with Week general value.

@Misbah 

Thanks for replay!

OK!

I can understand.