How to filter for YTD Values

brave14 · 2022-02-24T16:17:33+00:00

There was an error rendering this rich post.

I have a dataset that goes back to 2018 and its format is: Month Day, Year 12:00:00. I was looking to filter for rows only with a data between 1/1/22 and 2/21/22 on ETL but when I try to do this through the filter function, it doesn't display anything even though this dataset is updated daily through a live dataflow. Is there a way to do this in ETL or would I have to resort to SQL transform? I know you can filter the dates in analyzer but in this scenario, I can't do that.

This is how the format looks like in etl but before being imported, it looks like Month Day, Year 12:00:00.

Thanks.

Find more posts tagged with

Accepted answers

GrantSmith

@brave14

Try using a formula filter to convert your string you're using for comparison to a date:

`TicketCreatedDate` >= DATE('2022-01-01') AND `TicketCreatedDate` <= DATE('2022-02-21')

MarkSnodgrass

Here's the format I used in my sample dataset and it worked. Keep in mind, the preview window may not be pulling in all of the rows to accurately determine if your filter is working properly. You might need to run the full ETL, depending on the size of your input dataset.

DATE(`datereported`) >= DATE('1/1/2022') AND DATE(`datereported`) <= DATE('2/1/2022')

It may be the format I use for the hard-coded dates that are making the difference.

All comments

GrantSmith

@brave14

Try using a formula filter to convert your string you're using for comparison to a date:

`TicketCreatedDate` >= DATE('2022-01-01') AND `TicketCreatedDate` <= DATE('2022-02-21')

brave14

@GrantSmith After converting the timestamp to a date through the alter column feature (shown below), I then tried the filter formula you gave me but no rows are still showing. Thoughts?

MarkSnodgrass

I have seen this before. I would take what @GrantSmith is suggesting and wrap the date function around your field as well in the formula. DATE(`TicketCreatedDate`)

I think it is a bug in the ETL and have found that wrapping the date function around both works around the bug.

brave14

This is what I have so far but still no rows. @MarkSnodgrass

MarkSnodgrass

DATE(`datereported`) >= DATE('1/1/2022') AND DATE(`datereported`) <= DATE('2/1/2022')

It may be the format I use for the hard-coded dates that are making the difference.

brave14

@MarkSnodgrass @GrantSmith Thank you both for the help! It turns out the data was just not showing in the preview as you said and worked when I saw it after making it as an output.