Not 100% sure, but I expect [%MonthLength%] being 30 days. Not taking different month lenghts into account
[%YearLength%] is always 365 days. Not taking leap year into account.
If I had to choose, I would opt for [%YearLength%], least fault sensitive
My guess would be that this is caused by some other reason then this comparison. Probably caused by a combination of the full OQL and the data in the database. Hm, that is not very helpful….
Can you show us the full query as well as (an abstract of) the actual data that this OQL is querying.
The query is below – I did verify I can pull up the data fine online in the UI. Trying to think of the best way to show the data in an abstract format. Any suggestions?
'select USR.Name UserID, CAST(0 as DECIMAL) Placed, sum(AC.ACTIVITY_AMOUNT) Payments, ''MTDHist'' MetricType,
AC.ACTIVITY_DATE mindt, AC.ACTIVITY_DATE maxdt, ACC.Account_or_Policy AcctPol
from myapp.Archive AC
left join AC/myapp.Archive_Accounts/myapp.Accounts ACC
left join AC/myapp.Archive_Accounts/myapp.Accounts/myapp.Accounts_Account/Administration.Account USR
where (AC.ACTIVITY_DESCRIPTION = ''Payment'' or AC.ACTIVITY_DESCRIPTION = ''NSF'' or AC.ACTIVITY_DESCRIPTION = ''Transfer'')
and AC.ACTIVITY_DATE >= ''[%BeginOfCurrentYearUTC%] - [%YearLength%]''
group by USR.Name, AC.ACTIVITY_DATE, ACC.Account_or_Policy'
I guess I should explain the ultimate use case for this: I want to summarize values into a YTD, MTD, QTD table. Is there a better way than OQL to do this? Grouped by multiple fields. I don’t think a MF can handle “select xxx from y group by xxx “