How to filter for YTD Values

brave14
brave14 Member
edited February 2022 in Magic ETL

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.

image.png
image.png
image.png

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

Thanks.

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @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')


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @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')


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • brave14
    brave14 Member
    edited February 2022

    @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?

    image.png


  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • brave14
    brave14 Member

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

    image.png
    image.png


  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • brave14
    brave14 Member

    @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.

Categories