How to compare a date against the previous weekday


The chart will need to be created in SQL Mode.

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.

Set up a chart to base the calculations 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.

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 seven 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.’

Related Charts Help Articles

See more