This article explores how to use date values in your Excel workbook to drive Analytics Edge connector queries, opening the door to a more interactive reporting experience within Excel.
Analytics Edge provides a number of floating date range options that allow you to refresh reports without worrying about the date range. That lets you build reports for the past 7 days, or the last calendar month, but sometimes you want to see the data for a specific date range, and want to be able to enter it in a worksheet — ‘from’ and ‘to’ dates for the report.
If you haven’t yet seen the product, the Analytics Edge add-in lets you combine a series of wizard-based functions into an analysis workflow which we call a “macro” for convenience [It is not the same as an Excel macro, and does not require the special, macro-enabled workbook file]. As you step through the function wizards, Analytics Edge shows you what it is working with so you don’t have to think like a programmer.
If our worksheet has a couple of cells with ‘From’ and ‘To’ dates for our report (as pictured above), we can use the Read Worksheet function wizard to grab those cells.
Note that since the cells have labels above them, we read those as well and use them as a header row to name the “columns” of data. If those labels were not available, we would read the cells without a header row, and Analytics Edge would name them ‘Col A’ and ‘Col B’.
AE Step Results worksheet
When we execute the function, Analytics Edge shows us what it got by popping open a temporary worksheet with green highlighting. Here we can see that it has loaded our two date cells into columns named ‘From’ and ‘To’.
(read more about the temporary worksheet)
Required date format 20140301
Note that the cells are Excel dates, and can appear in many display formats. Most connectors need a very specific date format, so we need to convert it before we can use it. Using the Column – Convert wizard, we change the columns from Date to Text using the required format codes (yyyyMMdd).
Referencing column values
Our next step is to make a query using these dates, and we would do that using an Analytics Edge Connector; the Google AdWords Connector is shown here. Instead of selecting specific dates or a floating date definition, we can enter values for the start and end dates. With Analytics Edge version 1.5, we can reference the values in the current table by simply entering the column names in square brackets, i.e. [From] and [To].
Dynamic dates
When the macro is run, the actual values from the current table in memory will be used, and in this case, those values came from the worksheet. To run the report for a different date range, enter new values in the worksheet and run the macro — the query will use the new dates you entered.
This is just one example of how you can easily customize your analysis using Analytics Edge — without becoming a programmer! Simple!