I was looking to create a formula that required averaging based on the number of week 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 straightfoward.
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.
Hey there, This website is amazing! Keep up the good posts. I just bookmarked it
If your physical database is Oracle you could do something like this:
Evaluate('to_char(%1,%2)',Evaluate('last_day(%1)' as varchar(10), "Indirect Sales”.DIM_TIME.MONTH), 'W' )