Arbitrary Periods as Pivot Table Columns

Options

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?

Comments

  • RobSomers
    Options

    @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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**