OBI Time Dimension Weeks In a Month – Prototype Hack
Author: Christian Screen | 1 min read | February 5, 2010
I was looking to create a formula that required averaging based on the number of weeks in the given month. As an example Monthly Sales Total / # Weeks In Month. Since this is a fiscal calendar the weeks are usually standardized and shouldn’t change that often. So, if the fiscal calendar is on a 4-4-5 or 4-5-4 the logic is rather straightforward.
I couldn’t make changes to the underlying Calendar dimension table and I couldn’t recall how to do this programmatically in OBI so I went with what I will call a quick prototyping hack. Here it is below.
CASE WHEN “Indirect Sales”.DIM_TIME.MONTH_NUMBER IN (3, 6, 9, 12) THEN 5 ELSE 4 END
This worked out perfectly as a place holder as we can see by a quick ad-hoc look at the new calculated measure.
If anyone has the right or clever way of doing this leave a comment.
