Count based on newest entry for a specific field

[jalanhart]

Hello, 

I am trying to see if it is possible to create a formula for a KPI which shows the count of how many items are have a chosen status based on the newest entry per item. 

So we have lots of items with serial numbers that go out to customers and are returned to inventory. the table created by entries in the ERP has a new record created each time it's either delivered to a customer or received back in to inventory. 

I've tried some max date formulas but that just gives me the newest date total, but what I need it to do it find the max date PER SERIAL NUMBER and tell me the status of it. 

So I'm looking for a way that will count how many serial numbers have a status of "Issued" based on the last record entered for that serial number. If part A's last updated record is yesterday, part B's was one week ago, and part C's was a year ago, it needs to count each of those newest entries regardless of when the Max date is.

[pedrohenriqueperna]

Hi, @jalanhart 

It seems to me that you need to aggregate the count by serial number, something like:

Sum(Aggr(Count(Distinct {<status = {'Issued'}, status_date = {"$(=Max(status_date))"}>} serial_number), serial_number))

Basically, if you look for the max status date only, it will give you the max status date present in your data model, so you must aggregate by ID to look for the values individually

[jalanhart]

Thanks for the help. Not quite working for me. it's still giving me only one record for the max date, and the filter for Status is not working right either. I've attached a photo drilling down to one customer for example, and it should give me a count of 21 but instead it's just 3. 

[pedrohenriqueperna]

Can you provide a sample?

[pedrohenriqueperna]

Try this:

Count(DISTINCT {<serial_number = {"=Max(status_date) = Max({<status= {'Issued'}>} status_date)"}>} serial_number )