Calender-aware durations in DQL

[r_weber]

Likely more an RFE than a question...

Currently DQL only supports duration types of "ns" to "d" (here).
However especially with BizEvents you want to have something like calendar/week/month-aware durations and timeframes.
E.g. I'm tracking orders and revenue. I want to create a report/dashboard that shows data of this month/last month, last six months, this week, previous week.
I don't see how this can be done right now?

[sinisa_zubic · Accepted solution]

Hi @r_weber 

Currently there is not an easy way of doing this

Revenue of this month

fetch bizevents, from: bin(now()-duration(getDayOfMonth(now())-1,unit:"d"),1d, at: -1h)
| summarize sum(amount)

revenue of last month

fetch bizevents, 
  from: totimestamp(if(formatTimestamp(now(), format:"M") == "1", 
  concat(getYear(now()) -1,"-12-01-01"), else: (concat(getYear(now()), "-",toLong(formatTimestamp(now(), format:"M"))-1, "-01-01")))),
  to: bin(now()-duration(getDayOfMonth(now())-1,unit:"d"),1d, at: -1h)
| summarize sum(amount)

revenue of this week

fetch bizevents, from: bin(now()-duration(getDayOfWeek(now())-1,unit:"d"),1d, at: -1h)
| summarize sum(amount)

revenue of last week 

fetch bizevents, 
  from: toTimestamp(concat(getYear(now()-7d), "-01-01"))
    + duration((getWeekOfYear(now()-7d)-1)*7+1, "d"),
  to: bin(now()-duration(getDayOfWeek(now())-1,unit:"d"),1d, at: -1h)
| filter isNotNull(amount)
| summarize sum(amount)

revenue of last six month I think is also possible just with a more complex and nested usage of if function.

Best,
Sini