Beast Mode: Reverse Date Count
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 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.