Calender-aware durations in DQL

r_weber — Mon, 18 Dec 2023 09:43:52 GMT

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?

Re: Calender-aware durations in DQL

sinisa_zubic — Mon, 18 Dec 2023 10:50:29 GMT

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

topic Calender-aware durations in DQL in DQL

Calender-aware durations in DQL

Re: Calender-aware durations in DQL