Custom Expression: SDLY (Same Day Last Year)

Get Help
1

Hi, having difficulty creating a custom expression for same day last year. Example, I want to sumif a field for same day last year, eg: -365 days ago.

I have tried below but it doesn’t seem to be correct; any help would be appreciated!

Sumif([field], interval([date], -365, “day”))

2

Hey rolo,

Just to be clear, there’s a column you’d like to add data in another column based on dates that are exactly a year apart?

E.G.
10/12/2022 and 10/22/2023

It’s common to use self joins in cases like these as a Sumif may act as a cumulative component depending on how the table is structured.
Also, it looks like from your example, SumIf may not work in this case.

sumif is equivalent to case when you sum a single column for single condition.

You should use case if you want to sum a second column under a second, separate condition. For example, if you want to sum the Amount column when Status = “Shipped” and another (hypothetical) column like Refunded Amount when Status = “Refunded”.

For example, let’s say we have a basic table with “amount” and “sale_date”, we would join on date with the second component of the join being 365 days apart.

SELECT
    t1.sale_date AS first_date,
    t2.sale_date AS second_date,
    t1.amount + t2.amount AS total_value
FROM sales t1
JOIN sales t2 
    ON t1.sale_date = t2.sale_date + INTERVAL '365 days'

If we wanted to mimic this within the Question editor we can start by creating a new column that would represent the future date we would want to join on.

Then create a self join on ‘Day’.

Create a Total column

And aggregate the data

Depending on your database platform you may be able to use Windows Functions and SQL Query Editor to aggregate the information as well. Using the table structure from the above example, something like this should work.

SELECT
sale_date,
amount,
SUM(amount) OVER (PARTITION BY EXTRACT(MONTH FROM sale_date), EXTRACT(DAY FROM sale_date)) AS total_amount_by_date
FROM sales;

Just some things to play around with.

3

Thank you! I will give this a try - I was able to get it work using interval but more testing is needed to determine if this is a solid long term solution!