Finding latest date for a field, unless it has a null value

[#1]

Hi,

I am trying to create a table chart that shows an application and its latest scheduled date.

The data is coming from two separate tables, connected by a hostname key

An application can live on multiple hosts, and those hosts are migrated at different times, so one app can have several "scheduled dates".

What I need to do is find the latest date associated with an application.

But there's a catch... If one of the hosts that an app lives on has not been assigned a scheduled date yet, I need it to reflect that(ideally by displaying "Not Scheduled" in the date field).

So basically if all the hosts an app lives on are scheduled, show me the latest date. If at least one of the hosts an app lives on has not been scheduled, show me some sort of message informing me of that.

So far I have tried using the expression:

=max(aggr([Schedule Date],APPLICATION_NAME,[Host Name]))

But this will still return the latest scheduled date, even if some hosts have not been scheduled. I suppose this has something to do with the way Null values are handled but I am fairly new to QV and am not sure how to go about this.

Visually this is what I need :

Host name | App name | Latest Scheduled Date

host1         | App1        | 01/01/2017

host2         | App1        | 02/02/2017

host3         | App1        | NULL

With my current expression it will return 02/02/2017 since that is the latest date in its eyes, but I need it to return the NULL(or something in its place)

Thanks in advance,

Alex

[sunny_talwar]

How do you know the Null is the latest, is it because it is associated with host3?

[#3]

If it has a Null, then it has yet to be scheduled, which would make whatever date is eventually chosen the latest date

[#4]

the issue is that if any of the hosts of an app are not scheduled, I need to show that it is still waiting to be completely scheduled. But if they are all scheduled, i just need to see the latest date.

This way we can track which apps are on un-scheduled host

[sunny_talwar]

So essentially when you say Null, there will be 'NULL' written as text or there won't be any value present? Would you be able to share a sample?

[#6]

There won't be any value present

[effinty2112]

Hi Alex,

How do we know if an app is hosted on a particular host? There must be something other than a value for Latest Scheduled Date otherwise we can't differentiate between apps not present and those that are present but haven't been scheduled.

Cheers

Andrew

[#8]

The app to host relationships are predefined in the datasource, which i am making a RESTful API call to in order to retrieve data. The scheduling information is coming from a different datasource that is used for scheduling host migrations. API call 1 brings in all application-host data, API call 2 brings in all host scheduling information and is linked via the host Inventory #