Solution to Get Number of Business Days

[JerDale]

I am working with MSTR 7.2.2. The system will not allow me to create facts or tables. As a MSTR newbie, I am trying to learn how to create an object that will allow me to use the number of business days in a month in a calculation. "Month" is one of the date attributes available to me. I know the number of business days from the calandar and subtracting the number of company holidays. So 11/30/2004 = 20 business days, 12/31/2004 = 22 business days, etc. I noticed that when I put the Month attribute in a consolidation object, I can set the qualification for "number of business days" and it does filter correctly, but I don't know if I can or how to get at the business days using Month attribute.

My immediate goal is to divide monthly volumes for a product by the number of business days to get the average daily volume. This will be for more than one month (36 months max) and more than one product.

MSTR help files did not seem to cover this type of scenario, or if they did I would like to know under what subject.

Thanks.
Jerry

 

[entaroadun]

What level is your Volume fact stored at on the fact table? Is it on multiple fact tables with different levels?

 

[JerDale]

Volume fact is stored at the detail (product) level, and average volume for each month would be reported at that level. Average monthly volumes will always be reported by month, by product. All products are on the same level. I hope this ansers your questions.

Thanks.
Jerry

 

[entaroadun]

Is the Volume fact at the Product x Month level? Or is it a lower level on the time hierarchy?

 

[JerDale]

Yes, the system stores 36 months of data which, which can be aggregated by any combination of months, but I will be reporting by month and displaying 13 months product activity.
Our systems people now tell me that volumes are stored by customer account, by product. To find out if there are more levels, such as a table with product & volume, I will have to wait for persons who are out sick.

Jerry

 

[nlim]

the major reason why MSTR does not support it this way is that business days are different in every country due to holidays etc.

So how shall we solve this problem? One way is to create a metric called "number of business days". Let's try this approach, it assumes you have a day attribute that is child of month.

1) create metric M1 defined as DayOfWeek(Max(Day) {Day})
for each day, this metric will return a number 1-7, 1 is sunday, 2 is monday and so on.

2) create filter F1 defined as M1 between 2 and 6. ie mon to fri

3) create report R1 with day on row, and M1 on column. Add F1 to report. R1 will give you all the days that you consider business days. You can also exclude certain holidays in your country by adding to the filters. For example you can leave out New Year by adding "day not equal 1/1/2005". And so on.

4)create filter F2, drag R1 onto this filter. This is now a report-as-filter

5)create metric M2, count(day), conditionality F2

6)test M2 by creating report R2 with month and M2 on it. When you run, you should get the months and the number of business days in each month.

7)next create your ratio metric M3 as monthly volumes/M2. That will work as long as you have month on the report.

If you have no day attribute, then you will have to find a way to tell the system what business days are. Maybe edit the month lookup or use 8 with a logical view.

good luck

 

[entaroadun]

JerDale, where does the current "number of business days" come from? Is it already defined as a form of your Month attribute?

 

[JerDale]

Thanks for the responses.

I was looking for a solution where MSTR will allow me to create a metric using code that does this: '1/31/2002' = 21, '2/28/2002' = 19, ... etc., and I know this would require periodic maintenance. The solution with this formula was provided for me in a metric by our company systems support person:

([Volume] / [BUSINESS_DAYS_ALL_FACTS] {~} )

It was explained to me that BUSINESS_DAYS_ALL_FACTS would go against whatever tables my report will use, which will be at the product level, and retrieve the number of business days in the month to be used in the calculation that is in the metric.

This allows me to report average daily volumes using the number of business days in the month.

Jerry