OBI Time Dimension Weeks In a Month – Prototype Hack
Author: Christian Screen | | 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.
Blog Author
Related Posts
Oracle BI Publisher (BIP) Tips: Functions, Calculations & More
Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.
How to Index a Fact Table – A Best Practice
At the base of any good BI project is a solid data warehouse or data mart.
Qlik vs. Tableau vs. Power BI: Which BI Tool Is Right for You?
Tableau, Power BI, and Qlik each have their benefits. What are they and how do you choose? Read this blog post for a quick analysis.