Using a formula expression is a common technique in Excel reports built using ShufflePoint, and I see that we don't have a blog posting about it.  In a nutshell, there are times when you need a query to be dynamic beyond what is provided by the timeframe and property parameters.  Two common scenarios are a) filtering out branded keywords, and b) special timeframes.  In this article I'll show a query formula which does the later.

In the attached workbook, you'll see there are rows labeled "today" and "today last year". These have, respectively, the formulas

=TEXT(TODAY(),"yyyy-MM-dd")

and

=TEXT(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),"yyyy-MM-dd")

On the day that I've written this blog, they have the values "2012-12-05" and "2011-12-05"

In the query cell is the following formula

="select metrics ga:visits on columns dimensions ga:medium on rows by ga:visits from default where timeframe 2012-04-01:" & B5 & ", 2011-04-01:" & B6

Which on today results in the cell having the string

select metrics ga:visits on columns dimensions ga:medium on rows by ga:visits from default where timeframe 2012-04-01:2012-12-05, 2011-04-01:2011-12-05

The reason for going to this extra trouble is that having made the query using this cascade of formulas, a refresh of the report will present results which reflect this "day to current date" logic without the need to manually change the query when the report needs to be refreshed at a later date.