I am trying to get the timecards created this quarter for last quarter

WorldWarHulk
WorldWarHulk Member
edited April 2021 in Charting

I am trying to get the timecards created this quarter (Q2) but for last quarter (Q1)

the reference dates are created date(current quarter) and end date(previous quarter)

so, I have this created in beastmode>>>

(case When `TC: Created Date` >= '4/1/2021' and `TC: End Date`< '3/31/2021' then 1 else 0 end)

how can I make the dates dynamic so that I can create a graph that also shows the timecards created on Q1 but was for Q4 2020 and so on.

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @WorldWarHulk


    I'd recommend you reformat you restructure your data to utilize a date offset dimension. You can have customizable offsets (in your case last quarter) for each of your dates so that you can easily do different period over period type analysis.


    I've written on this topic before: https://dojo.domo.com/discussion/comment/50540#Comment_50540

    You'd simply use a report date and then filter on the Last Quarter offset to have the dates from 3 months ago / last quarter. This can then dynamically update based on your date filters.


    @jaeW_at_Onyx has a good video of this process as well: https://www.youtube.com/watch?v=CDKNOmKClms

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

    Hi Grant


    thank you for the suggestions.


    but the problem I have is that, instead of 1 reference date, I have 2, the created date and the end date. wherein the created date should be for the current quarter AND the end date should be previous quarter.

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    it sounds like you have two questions

    1) how do i identify data that was created in the previous quarter. create a date dimension with columns Date. And Start of Quarter. Then you can JOIN to your fact table and identify if the data was made before the Start of Quarter.


    2) how to just show data of the current quarter. beast mode. OR add a binary column to your date dimension isCurrentQuarter (1 or 0)

    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"
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    it sounds like you have two questions

    1) how do i identify data that was created in the previous quarter. create a date dimension with columns Date. And Start of Quarter. Then you can JOIN to your fact table and identify if the data was made before the Start of Quarter.


    2) how to just show data of the current quarter. beast mode. OR add a binary column to your date dimension isCurrentQuarter (1 or 0)

    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"

Categories