Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
count({$<Orders.CalendarMthNo={$(vDateMaxMonth)},Orders.CalendarYr={$(vDateMaxYear)}, Orders.DayOfYear = {'<$(vDateMaxDayOfYear)'}>}distinct order_id)
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
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
Hi Ian,
how do you calculate vDateMaxMonth ?
Regards
Andrea
Sorry - thought I might miss one...
max({<Orders.CalendarYr={$(vDateMaxYear)}>}Orders.CalnerdarMth)
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
That was it - thanks!
You are welcome.
Best regards
Andrea