Arbitrary Periods as Pivot Table Columns

WillClark

I'm trying to build out a pivot table card and I'd like to use some arbitrary time periods for the columns. This Week, Last Week, Month to Date, Year to Date. I'm not sure how/where to define these periods so I can use them in the pivot table to both use them as labels for the columns but also to then use them to do the value aggregations correctly as well.

Can someone point me in the right direction?

RobSomers

@WillClark My company has a card that does something similar with YTD, MTD, last week, etc. You'll need to write a beast mode for each time period you want to calculate, and use the current date to determine what gets included. A YTD beast mode would look something like this:

case when YEAR('date') = YEAR((date(CONVERT_TZ( now() , 'GMT','your_timezone'))) then 'Amount' end

Then when you add your column just set it to the desired aggregation. Then you'll just go through use the available date functions in Domo to do the comparisons with the current date for the rest of your time periods:

• MTD: mm/yyyy of 'date' = mm/yyyy of current date
• This week: ww/yyyy of 'date' = ww/yyyy of current date
• Last week: ww/yyyy of 'date' + 1 week = ww/yyyy of current date