OQL criteria using %MonthLength% causes missing months

0
Hi – I’m using %MonthLength% in my OQL statement below. I’m noticing Dec-2019-Jan2020 missing from the below, but other months appear back to Jan2019. I’ve traced the code and don’t see why these intervening months are missing. Any ideas why this happens? Would using [%YearLength%] be better? Thanks for any tips. and AC.ACTIVITY_DATE >= ''[%BeginOfCurrentYearUTC%] - 12 * [%MonthLength%]''  
asked
4 answers
1

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

answered
0

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.

answered
0

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'

answered
0

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 “

answered