How can I filter or make a calculated column using the last available date?

Question & Answer
1

Hi. I have a dataset with many account id’s and each account id has different dates named “applied date”. I want to filter the data so it only takes into account the latest date for each account in a way I can have just one account for each date. I tried to use MaxOver function to create a new column that shows null values for every account id except those with the latest date for each account id, I planned to use this to filter out null values and keep only the account id with the latest date value but it doesnt work.
image

2

Hi,

Step 1: Add calculated field “maxAppliedateByAccountID” as… maxover(max({applied Date}),[accountId])

Step 2: Add “maxAppliedateByAccountID” in table field well in values as custom

Step 3: Add calculated filed inclusion_flag as ifelse (max(appliedate)= {maxAppliedateByAccountID},1,0)

Step 4: filter by inclusion flag. Similar analysis is shown below using max(orderdate) over city

image

image
image968×294 9.93 KB

image
image1548×1268 143 KB

1 Like
3

@AnwarAli Yes thank you this is exactly how i pictured! Just one thing, do you know why i cant filter for values equal to 1 when doing visual calculations?

4

Hi, If you apply filter on inclusion_flag column it should work. Can you share detail what issue you are encountering while applying filter.

1 Like