Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chaitanyahublik
Contributor II
Contributor II

Getting Min timestamp from table data

Hi Folks,

I have below table data. I want to pick up Min(timestamp) against each cust. 

CustTimestamp
Debi1/12/2021 9:08
John4/10/2021 11:08
Debi1/14/2021 6:43
Alex3/3/2021 23:11
John1/16/2021 9:08
Sam1/17/2021 9:08
Sam1/18/2021 18:28

 

Expected outcome 

CustTimestamp
Debi1/12/2021 9:08
John1/16/2021 9:08
Alex3/3/2021 23:11
Sam1/17/2021 9:08

 

So basically I want to show what is oldest transaction time against each customer. I tried various option like using 

timestamp(aggr(min(TimeStamp),Cust),'YYYY-MM-dd hh:mm:ss') but it is not calculating correctly. Any ideas ?

 

Thanks,

C

@jagan @sunny_talwar 

2 Replies
Sammy_AK
Creator II
Creator II

as per the timestamp provided above, for some reason why i was not able to convert to timestamp, it gave wrong output. so i used makedate and maketime functions recreated the date & time and applied timestamp to get the correct output. applying min on this derived column gave the output. 

try this:

=Min(timestamp (MakeDate(
trim(subfield(SubField(Timestamp,' ',1),'/',3))
,trim(subfield(SubField(Timestamp,' ',1),'/',1))
,trim(subfield(SubField(Timestamp,' ',1),'/',2))
) +
MakeTime(
subfield(SubField(Timestamp,' ',2),':',1)
,subfield(SubField(Timestamp,' ',2),':',2)
)))

sunny_talwar

If your timestamp is correctly read as timestamp (meaning it is dual field with numeric value), you can just create a chart with Cust as your dimension and use TimeStamp(Min(Timestamp)). If it is not read as timestamp, then I suggest fixing that in the script using TimeStamp#() function

TimeStamp(TimeStamp#(Timestamp, 'M/D/YYYY hh:mm')) as Timestamp