Extract Min and Max Dates

[balanandam]

HI All,

I have some data like below:

loginTime	logoutTime	SessionID	username	Logindate
01-05-2018 05:07:18	01-05-2018 05:37:41	155	testing123	01-05-2018
01-05-2018 05:37:41	01-05-2018 05:37:46	156	testing123	01-05-2018
01-05-2018 05:37:46	01-05-2018 05:39:35	157	testing123	01-05-2018
01-05-2018 05:39:35	01-05-2018 07:17:26	158	testing123	01-05-2018
01-05-2018 07:17:26	01-05-2018 07:21:52	159	testing123	01-05-2018
01-05-2018 07:21:52	01-05-2018 07:22:11	160	testing123	01-05-2018
01-05-2018 07:22:11	01-05-2018 07:23:00	161	testing123	01-05-2018
01-05-2018 07:23:50	01-05-2018 07:25:43	162	testing123	01-05-2018

I wanted min & max dates for each day by user. Can you help me out here ?

Attaching App & sample data in Excel

Thanks In advance!

Br, Balanandam

[rubenmarin]

Hi Balanandam, time is loaded as a string, you need to convert to time using timestam#(), ie.:

=Timestamp(min(Timestamp#(loginTime, 'DD-MM-YYYY hh:mm:ss')))

[balanandam]

Thanks Ruben for the response!

I need data like, one row for every unique combination of Date,username. I believe I need to apply aggr() in expression.

Minmum date group by Date & username combination

Data I need as below:

testing123	19-04-2018	19-04-2018 11:53:01
testing123	20-04-2018	20-04-2018 00:47:29

Br, Balanandam

[rubenmarin]

Not really, if you alredy have username and date as dimension there is no need to use aggr, the default behaviour is to calculate the measure for each combination of the dimensions.

[balanandam]

Hi Ruben,

some how it is not working...

if you see app attached by you the Date & username combination is repeating...

Br, Balanandam

[rubenmarin]

That would be because these are different dates, althought you only see the date internally it's storing the time.

You can load using floor to ignore the time and have only one date, or use a calculated dimension that removes the time.

[balanandam · Accepted solution]

Awesome!!