Problem with a Date related Sum

[#1]

I have a report which gives Daily and Month to Date sales figures. As we've just gone into December it's thrown up a problem with the a few of the formulas which are based on the number of days in the month. For example:

//Month To Date

count({$<Orders.CalendarMthNo={$(vDateMaxMonth)},Orders.CalendarYr={$(vDateMaxYear)}, Orders.DayOfYear = {'<$(vDateMaxDayOfYear)'}>}distinct order_id)

//Budget MTD

sum({1<Orders.CalendarYr={$(vDateMaxYear)},Orders.CalendarMthNo={$(vDateMaxMonth)}>} ((Budget_Distinct_Order_id/vDateTotalDaysInMonth)*vDateMaxDayOfMonthLessOne))

With the variables being:

vDateMaxYear: =max(Orders.CalendarYr)

vDateMaxDayOfYear : =DayNumberOfYear(max(order_date))

vDateTotalDaysInMonth: =Day(monthend(max(order_date)))

vDateMaxDayOfMonthLessOne: =max(order_date)-1

These worked fine all month, but this morning they didn't populate and I'm 99% certain it's because of the change in month.

I'm also pretty certain that if I could figure out how to create a variable that would give me the month for yesterday it should sort out most of the issues, but for some reason it just doesn't seem to like anything I try.

Any help would be very gratefully received!

Ian

[anlonghi2]

Hi Ian,

how do you calculate vDateMaxMonth ?

Regards

Andrea

[#3]

Sorry - thought I might miss one...

max({<Orders.CalendarYr={$(vDateMaxYear)}>}Orders.CalnerdarMth)

[anlonghi2 · Accepted solution]

The problem could be in the formula used to calculate vDateMaxDayOfMonthLessOne because at the beginning of the month  max(order_date)-1 return 0 (if order_date is equal to the number of the month day) or the last day of the previous month (if order_date is a valid date) and so the result is a zero or null value.

Let me know

Andrea

[#5]

That was it - thanks!

[anlonghi2]

You are welcome.

Best regards

Andrea