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
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
chinnuchinni
Creator III
Creator III

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

View solution in original post

8 Replies
sasiparupudi1
Master III
Master III

use the expression

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

or

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

Anonymous
Not applicable
Author

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

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
Not applicable
Author

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

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

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

Anonymous
Not applicable
Author

Thank you for your reply !

It's what I was looking for.

mloy3
Contributor II
Contributor II

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?