Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pedrogomes93
New Contributor II

Discover minimum date for each day, for each user

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 iddate of loginrecord type
3031/2/2017 1:04:24 PMtype1
3031/2/2017 2:01:39 PMtype1
3031/2/2017 2:03:31 PMtype1
8081/3/2017 1:02:21 PMtype1
8081/4/2017 4:04:24 PMtype1

What I want to get is the following:

user iddate of loginrecord type
3031/2/2017 1:04:24 PMtype1
8081/3/2017 1:02:21 PMtype1
8081/4/2017 4:04:24 PMtype1

Thank you in advance

Tags (1)
1 Solution

Accepted Solutions
chinnuchinni
Contributor III

Re: Discover minimum date for each day, for each user

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.

test12.PNG

7 Replies
sasiparupudi1
Honored Contributor III

Re: Discover minimum date for each day, for each user

use the expression

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

or

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

pedrogomes93
New Contributor II

Re: Discover minimum date for each day, for each user

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
Contributor III

Re: Discover minimum date for each day, for each user

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

pedrogomes93
New Contributor II

Re: Discover minimum date for each day, for each user

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
Honored Contributor III

Re: Discover minimum date for each day, for each user

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
Contributor III

Re: Discover minimum date for each day, for each user

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.

test12.PNG

pedrogomes93
New Contributor II

Re: Discover minimum date for each day, for each user

Thank you for your reply !

It's what I was looking for.

Community Browser