Choose "Year" in Page Level Filter - display Year and Prior Year
I have a brain bender (at least it has been for me). I have a card I want to display "Sales vs Prior" - if the user chooses a "Year" (2015, 2016 or 2017) in the page analyzer I want this card to display the year they picked, and the year prior to that choice as well.
Any ideas on how to approach? I have attached sample data as well.
This is what I would want the user to see if they picked "2017" as the year (2016 and 2017).
This is what I would want the user to see if they picked "2016" as the year (2015 and 2016).
The series values interact with the raw data but not the time picker, meaning the series years don't really care what you choose for this year. They're just going to be absolute values, not relative.
One way I've gotten around this is to bring the data into a dataflow and join it back on itself, adjusted for one year. The new column(s) would be, then, a "prior year amount" in addition to "amount".
That way, whatever time you choose, there will always be another "prior year amount" next to your "amount", and the prior year calculation will already have been done on the dataside instead of in the card.
Something like this
,b.amount as prior_year_amount
JOIN sales_table b on a.date = dateadd(b.date, interval 1 year)
There will be more details to iron out than that, but hopefully that gets the ball rolling for you.
Also be on the lookout for period over period cards coming soon. They just might clear things up.Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Thanks for the information Aaron! I am going to start working on this now.1
I wanted to say thanks again! I got this working, and it is doing exactally what I had hoped.
Thanks so much for taking the time to provide a detailed answer to my post.2
- 7.7K All Categories
- 2 Connect
- 918 Connectors
- 244 Workbench
- 476 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 35 Visualize
- 198 Beast Mode
- 2K Charting
- 8 Variables
- 16 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 24 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 15 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部