Indicate if record has not come through yet

[IanCockcroft]

Hi guys, I have a bit of a condundrum which I am struggling with.

I have a SQL table that gets updated with various system status data.

if a system runs and finishes with a success or failure, a record is written to the table alng with the  date.

Depending on if the process was a success or failure, an icon is displaid. this works fine.

The problem is, when a date is selected where no record has come through yet, a late icon to be displayed.

something like this

date	status	URL_ID
2020-07-01	Success	1
2020-07-02	Success	1
2020-07-03	Success	1
2020-07-04	fail	2
2020-07-05	Success	1
2020-07-06	Success	1
2020-07-07	Success	1
2020-07-08	fail	2
2020-07-09	Success	1
2020-07-10	Success	1
2020-07-11	fail	2
2020-07-12	Success	1
2020-07-13	Success	1
2020-07-14	Fail	2
		3

If the 15th is selected in a filter, no record exists, so a URL_ID of 3 needs to be assigned and the reevant icon show.

Any ides guys ?

thanks

Ian

[lbendlin · Accepted solution]

Use the date from your dates/calendar table as the basis of your visual, not the event date.  Then you can do calculated columns or measures as needed to compute the status for each dates[date], via crossfilter(,,none) if you can create inactive relationships or other table math if not.

Here's an exampe of a calculated column in the dates table with relationship between the date fields.

[IanCockcroft]

aaaaaaahhhhhhhh, of course, the date dimension

thanks so much lbendlin 

I'm sure I can make it work

ian