Count virtual aggregate based on time window in API docs

REST API
1

How can I add a virtual aggregate function to count based on the timestamp of the record? I tried Count(Case when Date between DateAdd(day,-7,GetDate()) and GetDate() then 1 else 0 end) and it aggregates, but it duplicates the total in the Total aggregate field without date restrictions.

2

I figured out the solution. This is how to add a virtual aggregate field to get a “This Week” column to only count records from the last 7 days.

Add field, type = virtual

DB Function =
Count_BIG(CASE WHEN DateField > GETDATE() - 8 THEN 1 ELSE NULL END)

check the “aggregate DB Function?” box.

It’s actually more efficient to do this math in DreamFactory than in the sql.