3 Replies Latest reply on Jun 24, 2016 10:17 AM by Shinichiro Murakami

# How to return a NULL for a group of data (Date Column)

How can I return a "NULL" value in my Calculated Field when one of the dates in my cluster of data has a NULL date, and then return a MAX Date when all dates are available? See AUS-4 cluster below as an example. I would like to return a NULL. I am using this formula to get the MAX Date but I don't know how to return a NULL for the cluster.

{ FIXED [Cluster ID]: MAX(End Date)}

Cluster IDSite IDStart DateMAX DATE or RETURN a NULL (Start Date) at Cluster LevelEnd DateMAX DATE or RETURN a NULL (End Date) at Cluster LevelCOMMENTS
DFW-1DFW123

5/20

5/215/255/26
DFW-1DFW4565/205/215/265/26
DFW-1DFW7895/215/215/245/26
AUS-4AUS12346/106/257/1NullThis should return a null because one of the Site IDs has a NULL date for the AUS-4 cluster.
AUS-4AUS56786/106/25NullNullThis should return a null because one of the Site IDs has a NULL date for the AUS-4 cluster.
AUS-4AUS00016/136/256/30NullThis should return a null because one of the Site IDs has a NULL date for the AUS-4 cluster.
AUS-4AUS00026/256/256/30NullThis should return a null because one of the Site IDs has a NULL date for the AUS-4 cluster.
• ###### 1. Re: How to return a NULL for a group of data (Date Column)

Here is one idea.

[MAX DATE or RETURN a NULL (Start Date) at Cluster Level]

{fixed [Cluster ID]:max([Start Date])}

[MAX DATE or RETURN a NULL (End Date) at Cluster Level]

if

{fixed [Cluster ID]: count(if ZN(int([End Date]))=0 then [Site ID] end)}

=0

then

{fixed [Cluster ID]:max([End Date])}

end

Thanks,

Shin

9.2 attached.

1 of 1 people found this helpful
• ###### 2. Re: How to return a NULL for a group of data (Date Column)

Shin,