AQL query takes 30+ seconds

Question

I really need to optimize this, but having trouble.

FOR a IN accounts
      FILTER a.keywordsystem==true
        FOR k IN rotationpicks FILTER k.apikey==a._key 
             LET stats = (  FOR s IN rotationstats FILTER s.keywordid == k.keywordid AND s.apikey==k.apikey AND 
                  s.date == DATE_FORMAT(DATE_UTCTOLOCAL(DATE_ADD(DATE_NOW(),0,'hour'), "UTC"),'%yyyy-%mm-%dd')
                  COLLECT kwid=s.keywordid AGGREGATE clicks=sum(s.clicks)
                  RETURN clicks)
              LET clicks = (stats[0]==null?0:stats[0])
              SORT k.rpc DESC 
              LET final = MERGE(k, {clicks:clicks})
        RETURN final 

This part is the problem, there is only about 10 million records in that collection.

                  s.date == DATE_FORMAT(DATE_UTCTOLOCAL(DATE_ADD(DATE_NOW(),0,'hour'), "UTC"),'%yyyy-%mm-%dd')
                  COLLECT kwid=s.keywordid AGGREGATE clicks=sum(s.clicks)
                  RETURN clicks)

When I remove the date filter: s.date == DATE_FORMAT(DATE_UTCTOLOCAL(DATE_ADD(DATE_NOW(),0,'hour'), "UTC"),'%yyyy-%mm-%dd') Then it only takes a 1-2 seconds. I have an index on that date field and the format is stored in YYYY-MM-DD. Not sure why it's taking so long when adding in the date criteria.