Time dimension: have both calendar month and weekly

PrevContributor

We are looking to create a model where one can see both weekly and calendar month data for all measures. What is the best way? For instance we could create a week-based (e.g. 4-4-5) model, then insert formulas for time, e.g. January 2015 = (Week1 * 5/7) + Week2 + Week3 + Week4 + (Week5 * 6/7). Thanks upfront for the inputs. Sebastien.

aaron_overfors

Hello Sebasetien,

It depends on the efficiency desired and the type of visibility you would like. The most efficient and direct overall approach would be to create a model with Time settings of Weeks 4-4-5, 4-5-4, or 5-4-4. which allows for setting the timescale to Weeks or Months. This setup would be most efficient because the summary of the weeks timescale is in months, so if you would like a weeks input, you can enter data by week and see the months instantly calculate in the module. Or, alternatively, if you would like some combination of both, you can utilized breakback functionality to input on a monthly basis and spread it back to weeks, or have an instance of setting up dual modules (one with week timescale, one with month) and reference one or the other with a combination of a formula to calculate the desired result (For instance, if you have a months module with data and would like to distribute that over weeks, your formula in the weeks module could say, 'Months Module.Line Item * (.25 * Ratio)' to spread the monthly data over the weeks, similarly to the example you outlined). The biggest advantage of using this timescale is the interchangeability of the data sources since months are a summary of weeks.

Alternatively, you could use a Time setting of 13 4-week Periods, which will give you a period summary but will not align with calendar months. Weeks: General would be even more inflexible in this approach.

Hope that helps.

Thanks,
Aaron