5 Replies Latest reply on Jun 19, 2018 5:20 AM by Sohan Jawar

# How to calculate last date that returns other values by that date, but also including NULL values in the date field?

I do not really know how to correctly formulate this question, but I have 3 fields that are names, description and last date. I need to know how to return only last date per name, also giving priority to NULL date.

Can someone help me?

Note: I cannot share the workbook, since it has some sensitive data, however I can provide some pictures.

• ###### 1. Re: How to calculate last date that returns other values by that date, but also including NULL values in the date field?

Hello Augustas,

Can you try the below calculation:

{FIXED [NAME]: MAX(DATE)}

What do you mean by priority to NULL date.?

S

• ###### 2. Re: How to calculate last date that returns other values by that date, but also including NULL values in the date field?

Hey,

I have tried this calculated field, it shows correct values, but the thing is, it is not what I actually need. What I need is partialy this, like it gives me the last date, but as you can see in my picture, there is description attribute called: Running and it shows date as NULL, I need to make NULL date to be like the latest date. Is there any workaround?

Thanks.

• ###### 3. Re: How to calculate last date that returns other values by that date, but also including NULL values in the date field?

hi Augustas,

So you could populate the NULLs with either NOW() or the Max Date of the Data, something like...

[New_Date]

IF ISNULL([Date]) THEN {MAX([Date])} ELSE [Date] END

OR

IF ISNULL([Date]) THEN NOW() ELSE [Date] END

and then you can use Sudheer's formula, referencing this new field (not the original date field). You can then easily filter to just the last date for each Name

{FIXED [NAME]: MAX([New_Date)} = New_Date

Hope that solves the problem

1 of 1 people found this helpful
• ###### 4. Re: How to calculate last date that returns other values by that date, but also including NULL values in the date field?

Thank you! It actually helped me.

• ###### 5. Re: How to calculate last date that returns other values by that date, but also including NULL values in the date field?

Can you Try the below

IFNULL({MAX([DATE])},[Date])