Request: Last X Years/Months/Weeks, Excluding Today

[Pat Johnson]

We use SM in some of our forward projections, and in the case of smaller data sets throwing in partial days messes with these projections. Any chance we could see an 'exclude today' toggle to gather this data and avoid partial days?

[Pat Johnson]

To clarify, there is an 'Including this month', which we do use frequently, but that toggle includes today, where the data is partial. This is what we're trying to avoid without having to run tables on a daily granularity and sum them by month, which is time consuming.

[Mikael Thuneberg]

You can use a custom date range for that. As the start date, type something like "Jan 1 - 2 years" (to start from Jan 1 2014) and as the end date type "yesterday". That way the current day will be excluded from the results.

--
You received this message because you are subscribed to the Google Groups "Supermetrics" group.
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalyt...@googlegroups.com.
Visit this group at https://groups.google.com/group/automateanalytics.
To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/6478427b-857d-40b4-89fc-861b119238e3%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Pat Johnson]

That's a good idea, but what if I want a rolling range of months, excluding today? Say last 12 months, including this month, excluding today. If I define a custom range I'll need to keep pushing the start date ahead manually, wouldn't I?

[Twan]

Hello Pat,

You can use formulas in the SupermetricsQueries sheet to get rolling dates. For example, you can use =today()-1 at the end date to always get yesterday and =date(year(today()-1);MONTH(today()-1)-12;1) to always get the last 12 months. Keep in mind though, that if you apply changes to a query through the interface, the formulas will be replaced. So its best to have this formula somewhere else on the sheet and reference to this cell in the start_date and end_date columns like this =H5.

Op donderdag 16 juni 2016 22:28:48 UTC+2 schreef Pat Johnson:

[Pat Johnson]

That is great, this opens up a lot of possibilities. So to clarify, it doesn't populate the formula in the sidebar fields when you edit them into the actual query table on the SupermetricsQueries tab, so you're recommending I reference another cell with the formula to... make it so it does reference it? Or is that just so it doesn't wipe the formula every time I want to edit it and have to re-type it out every time.

[Mikael Thuneberg]

What Twan suggests is one way, using spreadsheet formulas. But you can also type anything accepted by PHP's strtotime function as the start and end dates. To get a rolling last 6 months + current month until yesterday, you could put start date = "first day of this month - 6 months" and end date = "yesterday". Or for rolling last 30 days until yesterday, start = "yesterday - 30 days" and end = "yesterday". You can type these values in the add-on sidebar to the start and end date fields. When you click them, it opens a calendar, but just ignore that and type in the value.

To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/abbef5ac-7f98-4262-988b-60dd71987ead%40googlegroups.com.

[Pat Johnson]

That is fantastic, it works like a charm. Thanks both of you.