Beast Mode: Reverse Date Count

user091749

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;

1.  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).
2. 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

jaeW_at_Onyx

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.

 

 

 

ST_Superman

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?

jaeW_at_Onyx

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.

 

 

 

user091749

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.