Filter to Compare on a table different years but same period of time?

LiliRestrepo
LiliRestrepo Contributor
in Charting

Hi!

I'm trying to compare a value (total hours) on a table, for the same period of time (this year vs last year) and I want to be able to do it with a filter.

Example:

User wants to see total hours between Sept 1st to Sept 5th for current year compared with last year same period.

The table that I'm using is a pivot table (simplified table below):

image.png

The problem with the actual date filter is: if I modify the card with a date range filter and I select 09-1-2022 to 09-05-2022 then I'm missing last year numbers, and if I select as a date range 09-1-2021 to 09-05-2022 then I'm adding the data between that period of time (From Sept 06, 2021 - Aug 31, 2022), not just the days I need.

I fixed it in a funky way by creating 2 different filters: Day number and Month Name and adding last 2 years as filter in the backend of the card. In that way if I select 1,2,3,4,5 and September it will show the exact numbers for those days - BUT the problem comes when someone needs to compare overlapping month periods as: Feb 25 to March 10th 2021 vs same days 2022.

In that case my filter wont work:

image.png

It would be great if we had a compare between date 1 and date range 2 filter :(.

Let me know if you have any ideas

Thanks!!

Lilia

Best Answer

Answers

  • LiliRestrepo
    LiliRestrepo Contributor


  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @LiliRestrepo

    I'd recommend looking into structuring your data differently to use a custom date dimension table. This will allow you to have data to compare it to the same timeframe from a year ago while allowing date filtering to still work properly. I've done several write ups of this in the past but you can find more information on how to do this here: https://dojo.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

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

    I created a video that shows how you can create a standard date, which then allows you to look at different years next to each other. My video uses a line chart, but you can switch to a pivot table and format the date fields as needed to get the look you want.

    https://youtu.be/mDwSQGAjIrs

    **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.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @MarkSnodgrass i was flipping through your tutorial. in your proposed solution the DateFilter is set to This Year + a couple extra years via YOY comparison option.


    as best you can tell, is Domo actually applying the DateFilter filter? I'm guessing not b/c Domo ought to be limiting you to either just this year, or the last n years per the DateFilter selection.


    @GrantSmith 's approach is a lot more effort to get off the ground, but once you get it, it's a much more extensible and guaranteed to work every time solution, IMHO

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • MarkSnodgrass
    MarkSnodgrass Coach

    @jaeW_at_Onyx here's a breakdown of how my card is set up that makes it work.

    I create a "standard date" beast mode field that takes the date field in the dataset that you need to do YoY on and moves it into the current year.

    DATE_ADD(`datereported`, interval (YEAR(CURRENT_DATE())-YEAR(`datereported`))   year)

    My year field is just extracting the year from the date field, which can be used for the series (or rows in a pivot table.

    YEAR(`datereported`)

    I create a quick filter that allows me to easily filter how many years back I want to look at with this beast mode:

    YEAR(CURRENT_DATE()) - YEAR(`datereported`)
    image.png


    The date range filter is based off my "standard date" beast mode which is why it will always show the current year in that range, but that year is actually meaningless. The Graph by is more important so you can choose what granularity you want to compare against.

    image.png

    Here's what it looks like as a pivot table.

    image.png

    It proves to be quite flexible using the quick filter to let you decide how many years you want to look at.

    **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.
  • LiliRestrepo
    LiliRestrepo Contributor

    Hello!

    Thank you SO MUCH everyone for the answers, @GrantSmith I used your method and it worked, I really appreciate the help. We are a CPA company and the way we do comparisons require the data this way so it helped a lot.

    @MarkSnodgrass I tried your method too and it works when I want to compare full years but in this case the user wants to compare exact same date range for current year with same days last year. (Sept 1 to Sep 5 2022, to Sept 1 to sept 5 2021, etc). Maybe I missed something in the process.

    I really appreciate your help again!


    Lilia

Categories