Need to get only latest record for each combined_id in Pie Chart

[Akash2k]

In Pie Chart (Donut),
my Dimesion is job_status which has values SUCCEEDED,RUNNING,KILLLED,FAILED

my measure should be count of combined_id ,where timestamp=max(timestamp) group by combined_id, so i will get only the latest record for each combined_id

i dont want to implement in Load editor because there is timestamp filter pane if that changes it doesnt give the latest record for that combined id on the selected date range in the filter, so i need to implement in Pie chart

combined_id  |  timestamp  
A |  2023-07-31 15:33:16.66666
A |  2023-08-01 20:33:16.66666
A |  2023-08-01 05:33:16.66666

need A |  2023-08-01 20:33:16.66666
i have tried Count({<timestamp={"=max(timestamp)"}>}combined_id)
Count({<timestamp={"=aggr(max(timestamp),combined_id)"}>}combined_id)

Doesnt work, if i do this i am not getting the latest record for each combined_id
(please dont give max(timestamp) group by  combined_id and then Left join--> this is for all the data, then it wont be right if i applied any filters for a particular range of date)

Please help me on this
Thanks in advance

[ckarras22]

Could you please check this one:

Count({<timestamp={"$(=max(timestamp))"}>}combined_id)

[BrunPierre]

Hi @Akash2k maybe this.

=Count(DISTINCT Aggr(If(Max(TOTAL <combined_id> timestamp) = timestamp, timestamp), combined_id, timestamp))

[Akash2k]

Hi @ckarras22 

this doesnt group for each combinedid 

if i select timestamp filter from 1st aug to 8th aug 

for all combinedid it is comparing with 8th aug as max,

but ty for your help

my timestamp format is

YYYY-MM-DD hh:mm:ss.ssssss

[Akash2k]

Hi @BrunPierre 

i am not getting any count if i am using this expression in my chart

may be i think because of using if in the expression

I  am not sure but i could understand the expression like 

Max(total <combined_id> timestamp) finds the maximum timestamp across all combined IDs, not the maximum timestamp for each combined ID .

When used within the  if function, it checks if the current row's timestamp is equal to this overall maximum timestamp, which is not what i typically want

but thank you for your help

[BrunPierre]

It seems to be working just fine. Format the DateTime properly as below;

Timestamp(Timestamp#(timestamp,'YYYY-MM-DD hh:mm:ss.fffff'),'YYYY-MM-DD hh:mm:ss') as timestamp

[Akash2k]

Oh Ok it is working in table chart

another condition i have to consider only the records which has BATCH in jobpattern

i have attached my sample data and screenshots

ERROR that i am facing:
even in pie chart it is showing correct count but if you look on the jobstatus FAILED there is no count

but when i selected that FAILED status it is showing that has 2 combinedid count, i dont want to show like that.

and in table chart also i want to show only those latest records is there a way to show only that count which is not zero

thank you  so much for your help

[BrunPierre]

=Count(DISTINCT{<event_timestamp = {"=Aggr(nodistinct Max({<job_pattern={'BATCH'}>}event_timestamp),combined_id) = event_timestamp"}>} combined_id)

[Akash2k]

Hi 

i have attached sample2 data

The expression doesnt work on my sample2 data file which i have attached. I can see only one combinedid

can u please look on to that

i cannot find the reason why some combinedid are missing while using the expression that you gave

thanks for helping this far

[BrunPierre · Accepted solution]

=Count(DISTINCT {<job_pattern={'BATCH'}>}If(Aggr(NODISTINCT Max(event_timestamp), combined_id) = event_timestamp, combined_id))