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

WorldWarHulk

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.

GrantSmith

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

WorldWarHulk

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

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)

jaeW_at_Onyx

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)