Find all the columns for the maximum time.

[paulista]

I have the data as shown above , i want to find the max timestamp related to an id and corresponding activity and evt_type.

so there would be just one row for a unique id and the related fields.

attached is the data sheet

Please help me the feedback . thanks 

[paulista]

count(distinct if(HDR_TIMESTAMP = aggr(NODISTINCT max(HDR_TIMESTAMP), HDR_CORR_ID), HDR_CORR_ID))

it gives me the correct count when i type this.

not when i take hdr_event_type, why would that be ?

also when i click on the value it changes to a value which is not a correct number .

[zhadrakas]

with your current statement you are calculating the max(timestamp) per ID, which is not what you wanted.

You wanted the max(timestamp) per ID & Event & Activity.

So you need to aggregate over this three dimensions:

count(distinct if(TIMESTAMP = aggr(NODISTINCT max(TIMESTAMP), ID, [Activity Name], EVT_TYPE), ID))

count(distinct if(TIMESTAMP = aggr(NODISTINCT max(TIMESTAMP), ID, [Activity Name], EVT_TYPE), ID))

[paulista]

see th table in left should guve me 34 total, but if you see at distinct events , they dont add up to 34 but much more

table on the left shows me a total of 34 but yif you add up the individual counts its much more , it should add upto 34