How to compare a date against the previous weekday using a dashboard filter

Select a date and compare that date to the same weekday one week ago, two weeks ago, et cetera.

The chart will need to be created in SQL Mode, so some basic knowledge of SQL is required.

Create a categorical dropdown

From the Dashboard, choose Add Element > Categorical Dropdown. Select your data source, and drag your date column to the Dimensions field.

Choose a name. Ours is titled BASE because it is the date which the other calculations are based on.

Create your chart

From the Dashboard, choose Add Element > Chart to create a new chart. 

This example uses a PostgreSQL database. If you are using a different database type, the syntax may differ.

Our first dataset selects the sum of Cost on the date selected in the dropdown. It looks like this:

SELECT "Marketing"."description" AS "Description",
 SUM("Marketing"."cost") AS "Base"
FROM "public"."marketing" AS "Marketing"
WHERE ("Marketing"."created_date"::DATE BETWEEN {BASE} AND {BASE})
GROUP BY "Description"

Next we'll add a second dataset to filter by 7 days before the base date.

SELECT "Marketing"."description" AS "Description",
 SUM("Marketing"."cost") AS "-1 week"
FROM "public"."marketing" AS "Marketing"
WHERE ("Marketing"."created_date"::DATE BETWEEN {BASE}::date - interval '7 day' AND {BASE}::date - interval '7 day')
GROUP BY "Description"

To filter by 14 days ago, simply copy the query from your second layer into a third dataset and change '7 day' to '14 day'.