Arbitrary Periods as Pivot Table Columns
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
-
@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!**
2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive