_day_of_year/month/week etc... Help

[zb12]

I need to divide a number by the day of the year and I also need to divide a separate number in the same report by the number of days that were in the previous month.

So for example if the day the report is ran is 3/4/2009 and I need to divide by the last day of February. So first, how would a write out the formula for the year? "#/_day_of_year(?)" it should equal 59. Then how would I divide the other number by the days that were in February (28) Can I use the _last_of_month expression or perhaps _days_to_end_of_month? and what would that look like?

[rockytopmark]

Do you have a Time Dimension?  Typical Time dimensions have a column that is either the day in the date's month that is the last day, or a boolean that is like "Is last day of month"  and similarly, has integer type columns for the day of year, month, quarter, etc.. .... such columns in the Time Dimension make what you are doing trivial.

If you don't, then you need to calculate to a date in the prior month, then use functions to determine the day, then some more to get the number of days in the year.  This gets messy, but is doable.

Start with the current_date expression and work your way back....

Prior Month:
_add_months(current_date,-1)

Last day of prior month:
_last_of_month(_add_months(current_date,-1))

Finally, the number of Days in the year, up to the end of prior month:
_day_of_year(_last_of_month(_add_months(current_date,-1)))

...and the number of days in the prior month:
_day_of_month(_last_of_month(_add_months(current_date,-1)))

[zb12]

The number or days in the year works great but the number of days in the prior month is not working. here is what I have and remember these are for two separate columns.

for the Month to Date column: [Demand Deposits].[DDA MTD].[Curr MTD Aggregate Balance]/_day_of_month(_last_of_month(_add_months(current_date,-1)))

this used to look like this: [Demand Deposits].[DDA MTD].[Curr MTD Aggregate Balance]/28


and for the Year to Date column I now have this: [Demand Deposits].[DDA YTD].[Curr YTD Aggregate Balance]/_day_of_year(_last_of_month(_add_months(current_date,-1)))

this used to be: [Demand Deposits].[DDA YTD].[Curr YTD Aggregate Balance]/59


the error I'm receiving is this: An error occurred while performing operation 'sqlPrepareWithOptions' status='-56'.

Thanks for your help!

[blom0344]

SQL errors are usually well documented. All you need to do is open the detail-link in the error. Somewhere in the mass of text is the explanation on the error..

[zb12]

This is the details part of the error: DPR-ERR-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:time-date-#
Where do I need to go to read the complete error?

[zb12]

I was flipping through the functions list looking at the available components and I did not see the _day_of_month function. I have _day_of_week and _day_of_year. Does this matter? Any more suggestions?

[rockytopmark]

my bad... the day is actually the day of the month.

:-[

[zb12]

Ok the _day_of_the_month didn't work either. Any other suggestions?

[zb12]

Ok here's a new one...
What does the formula need to be for this expression if I am running the report on 3-15-2009? Basically I want to see all of the new accounts that were opened from the last time I ran the report:
[Account].[Date Opened] between 2009-02-15 and 2009-03-14

[MTeegarden]

Here's a formula that will return you a number equal to the last day of the previous month.

_days_between(_last_of_month(_add_months(current_date, -1)), _first_of_month(_add_months(current_date, -1))) + 1

Subrtact last day of prev month - First day of prev month and then add 1.

For some reason there is no function to just pull out the day, month or year part of a date.