how to automatically Generate Daily report from Data using query formula in google sheet

Question

initially, i use sumifs to generate daily report, but its problematic as our staffs have to change dates daily within the formula, i want a formula that can automatically pull out data for each day. I already Tried today() function , but it i wont be able to save data for daily sheet, the second sheet shows exactly what i want using sumifs formula. is there any query formula that can pull out the data on daily basis? i already solved the weekly report using query formula.

here is a link to my google sheet

={QUERY(A1:I," select A, B, sum(D),sum(F),sum(H),sum(F)-sum(H) WHERE A =date '2021-08-02' and B is not null group by A,B order by sum(D) desc limit 1000 label A 'Dates',B 'Customers',sum(D) 'TOTAL RMB', sum(F) 'Total Naira',sum(H) 'Total Received',sum(F)-sum(H)'Net Balance' "); {" ","Total",sumif(A2:A,date(2021,8,2),D2:D),sumif(A2:A,date(2021,8,2),F2:F),sumif(A2:A,date(2021,8,2),H2:H),sumif(A2:A,date(2021,8,2),F2:F)-sumif(A2:A,date(2021,8,2),H2:H)}}

how can i possibly automatically generate daily report using query formula without having to manually edit the date daily

When you say you've tried TODAY(), Have you tried it like this WHERE A =date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' ?? — Nabnub
yes i did, the problem is that we often need to check yesterdays balance or the day before, is it possible to create a button the can pass the date to it? — joseph afolabi

Nabnub Nabnub · Accepted Answer · 2021-08-09T10:24:47

It seems that maybe you just need a cell reference where users can input any date then retrieve the desired results:

I've chosen J1, but you can have the user enter a date wherever you want:

WHERE A =date '"&TEXT(J1,"yyyy-mm-dd")&"'

This method does not required users to modify the formula, in fact you can even protect the formula from editing and let user edit J1 only (if necessary)

(Same thing for your SUMIF)

how to automatically Generate Daily report from Data using query formula in google sheet

2 Answers