Sunday, December 29, 2013

yogi_Compute Key Business Metric Total Active Students In Last 30 Days Directly From Data In FormResponses Sheet

                                          Google Spreadsheet   Post  #1458
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Dec-29, 2013
question by Universal Account (!mydiscussions/docs/g0NE6a0Mtcw)
How to combine filter and google clock in a way that works?
Why does the following not work?

=COUNTA(FILTER(CK:CK;CK:CK="Open", A:A>=datetime '"&text(GoogleClock()-30,"yyyy-MM-dd HH:mm:ss")&"'))

I know I can get a rolling 30-day view of data using query and google clock in this manner. Can I not use it in this format for filtering data? Related, how do I intelligently filter by month only - also based on Google Clock. For instance, always show 1 month back in relation to current time? I see many examples for calculating static date ranges in this forum; I have not encountered any using filter.

Appreciate any help immensely!
Example spreadsheet: 

FormResponses comes from a Google Form. It contains Enrollment Info for a school. Imagine it has 500 entries, with dates spread across months and years.