Cognos Transformer including public holidays for MTD and YTD

[twister]

Hello Transformer Gurus,
I have a public holiday indicator column in my date dimension table.
Using this column, I need to calculate the following :
1) Total No. of working days per month and year. This is not a measure. What is the best way of showing in the cube?
2) There is a revenue measure. I have to calculate MTD & YTD revenue and find out the average based on no. of working days in the month and year.

What is the best way of doing it?

Thanks.

[wyconian]

Hi

Working days is always a bit of an issue.  This is what I've done in the past, maybe it will give you what you want.

The first thing is to add a column to the date dimension in the database/warehouse.  I have this set to 1 for a full working day, 0 for a public holiday/weekend and 0.5 for part working days (you may not need that).  I then sum this new column from a specified date (usually the earliest date in the date dimension).  That gives you the sum of working days from this date for each date in the dimension.  To get the number of working days between two dates you need to subtract the working days from each date.

If you need to have a number of working days for a month/year you may need to have this new column based on the first day of the month or first day of the year, then you can sum these.

Hope that makes sense, good luck :-)

[CharlesAZ]

Hello Transformer Gurus,
I have a public holiday indicator column in my date dimension table.
Using this column, I need to calculate the following :
1) Total No. of working days per month and year. This is not a measure. What is the best way of showing in the cube?
2) There is a revenue measure. I have to calculate MTD & YTD revenue and find out the average based on no. of working days in the month and year.

What is the best way of doing it?

Thanks.

What I did was use that Indcator field as a Dimension in my Model.  Then I did a Category count measure in Transformer at the day level,  Now I can get my working days per month, and use it in a number of different MDX functions via Report Studio, such as a Tuple;  Tuple(MTD, business day Indicator, Revenue)

Works like a charm  :)

Hope this helps!

Charlie