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.
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.