Discover minimum date for each day, for each user

[Anonymous]

Hello,

I am struggling with finding a way to know the minimum date of each day for each user in QlikSense

This is the data I have: (date of login is M/D/YYYY)

user id	date of login	record type
303	1/2/2017 1:04:24 PM	type1
303	1/2/2017 2:01:39 PM	type1
303	1/2/2017 2:03:31 PM	type1
808	1/3/2017 1:02:21 PM	type1
808	1/4/2017 4:04:24 PM	type1

What I want to get is the following:

user id	date of login	record type
303	1/2/2017 1:04:24 PM	type1
808	1/3/2017 1:02:21 PM	type1
808	1/4/2017 4:04:24 PM	type1

Thank you in advance

[sasiparupudi1]

use the expression

Aggr(Min([date of login]),[user id])

or

Aggr(Minstring([date of login]),[user id])

[Anonymous]

Hi Sasidhar,

Thank you for your reply.

This works if I want to get the minimum date for each user Id, but it doesn't get me the minimum date for each of the days.

I want to be able to have only the first date (minimum date) for each day that the User ID has a record.

[ychaitanya]

why is the user 808 repeated 2 times in your expected output ?

as you said you need to find min date of login for each user , am surprised to see 2 entries for 808 ?

i have tried table with userid, recordtype as DIM and =Aggr(Minstring(dologin),userid) as expr .

it worked,

Can you check

Thanks

CY

[Anonymous]

Hi Chaitanya,

The reason why I have the user 808 repeated 2 times is that I want to have the minimum date for each day.

In this case, user 808 has a record in 2 days, so I want to present both days

Thank you for your reply

[sasiparupudi1]

May be try

A

T1:

Load

[user id],

TimeStamp(Timestamp#([date of login],'M/D/YYYY h:mm:ss TT'),'M/D/YYYY h:mm:ss TT') As [date of login],

Date(Floor(Timestamp#([date of login],'M/D/YYYY h:mm:ss TT')),'M/D/YYYY') As [date of login1],

[record type]

Inline

[

user id,date of login,record type

303,1/2/2017 1:04:24 PM,type1

303,1/2/2017 2:01:39 PM,type1

303,1/2/2017 2:03:31 PM,type1

808,1/3/2017 1:02:21 PM,type1

808,1/4/2017 4:04:24 PM,type1

];

Left Join(T1)

Load

[user id],

[date of login1],

Min([date of login]) as [date of login],

1 As Flag

Resident T1

Group By

[user id],

[date of login1]

[chinnuchinni · Accepted solution]

Hi,

take the day as filter from date field.

the in straight table take the dimensions are Day, User Id,record type.

and exp:

aggr(MinString([date of login]),[user id],Day)

.then if you want you can hide the Day in presentation tab.

[Anonymous]

Thank you for your reply !

It's what I was looking for.

[mloy3]

i tried to use similar approach, but i will need this to work on measure instead. Would it be right to say that this method is not applicable on Measures?