Beast Mode: Reverse Date Count
Hi All,
I have a database containing project information updated on a day by day basis. A project will have an associated RAG (red, amber, green) status and the corresponding date.
For example, Project X was red for 10 days, then green for 3 days, and red for 7 days up to today.
I need a statement which, first, identifies all the projects that are Red as of today. Followed by a statement saying, for those projects find how long they have been in this current Red status. Essentially, counting backwards the number of days it is in that Red status until it changed.
So that I can make a horizontal bar graph, with project name on y-axis and number of days in Red status as the x-axis (only for those that are Red today though).
The issue;
- The first statement locks Domo Date Range to today's date and subsequently, the second part only outputs 1 (because the information now available is only for today, so a total of one day).
- The first statement is disregarded and the second statement finds the total time in the Red status ever - so in the above example, it would return 17 days rather than 7.
Assume the following;
Today_RAG = RAG status dimension for today
Red, Amber, Green = Measures within the Today_RAG dimension
Might help: When putting the Today_RAG dimension as the x-axis, DOMO makes it a "Count of Today_RAG". Giving a horizontal numeric timeline of the periods it was Red, Amber or Green (with Today_RAG as the series too). All I need is this graphic but for those projects that are Red currently.
Let me know if you need more information with this.
Thanks, B.G
Best Answer
-
it sounds like you have a dataset with one row per day per project.
if you can transform your data I would
1) add a column that flags 'newState_Boolean': such that CASE WHEN YesterdayState = TodayState then 0 else 1 end. You can use the RANK() function in Magic to acchieve this or LAG (equivalent) in Redshift / MySQL.
2) add a column "newState_LastChangeDate" : such that CASE WHEN YesterdayState = TodayState then Yesterday_newState_LastChangeDate else ProjectDate. Basically you want to add a row that says if the state changed, then capture the project date, other wise keep the previous state change date.
What this will allow you to do is get the 'current row' for each project in your cards and do a datediff between project date and newState_LastChangeDate.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
Could you provide a small sample of your dataset? Maybe for 2 or 3 projects so that we can understand the structure of the data?
0 -
it sounds like you have a dataset with one row per day per project.
if you can transform your data I would
1) add a column that flags 'newState_Boolean': such that CASE WHEN YesterdayState = TodayState then 0 else 1 end. You can use the RANK() function in Magic to acchieve this or LAG (equivalent) in Redshift / MySQL.
2) add a column "newState_LastChangeDate" : such that CASE WHEN YesterdayState = TodayState then Yesterday_newState_LastChangeDate else ProjectDate. Basically you want to add a row that says if the state changed, then capture the project date, other wise keep the previous state change date.
What this will allow you to do is get the 'current row' for each project in your cards and do a datediff between project date and newState_LastChangeDate.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Hi,
That does work thanks!
I ended up manipulating the direct source code that feeds into DOMO, but it followed the same procedure as what you have outlined.
Thanks.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 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
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive