I'm trying to figure out how to dynamically update a section of my google query taking a value from another cell.
I'm filtering my query with a date, e.g. WHERE A Date > TODAY()-10
What I'm trying to do is have a value in a cell (P1), and have my today()-10 take the number from P1 and use that in today()-10
So if in cell P1 I put the value 20
my query would update to today()-20
I've tried using indirect, and also just referencing the cell but can't get either to work:
My latest attempt without indirect is:
=query(query('Data - Google GA'!$A:$H,"SELECT B, SUM(C), SUM(D), SUM(E), SUM(F), SUM(G), SUM(G)/SUM(E) WHERE A >= DATE '"&text(today()-"'&'"P1&"'&'","yyyy-mm-dd")& "' GROUP BY B ORDER BY SUM(E) DESC LABEL B 'Campaign', SUM(C) 'Impressions', SUM(D) 'Clicks', SUM(E) 'Cost', SUM(F) 'Sales', SUM(G) 'Revenue', SUM(G)/SUM(E) 'ROAS'",1),"SELECT * WHERE Col7 < 3.5",1)
I've created a simplified version here https://docs.google.com/spreadsheets/d/19FL5suHAnKYzeW7TVja52o-6TfAmBPhNpBcKGPOjLUw/edit#gid=0
In cell N4 I've added the formula I was trying.
thanks for any help!
