topic Re: LOGIN DETAILS LAST 3 MONTHS DATA in QlikView

LOGIN DETAILS LAST 3 MONTHS DATA

manoranjan_d — Wed, 09 Nov 2016 12:31:52 GMT

hi all,

below is my requirement and sample data

No of times user logged Past 3 months

data

note date format is MM/DD/YYYY

USERNAME	LOGIN HISTORY
x1	1/1/2016
x1	2/1/2016
x1	3/1/2016
x1	4/1/2016
x1	5/1/2016
x1	6/1/2016
x1	7/1/2016
x1	8/1/2016
x1	9/1/2016
x1	10/1/2016
x1	11/1/2016
x2	1/1/2016
x2	2/1/2016
x2	3/1/2016
x2	4/1/2016
x2	5/1/2016
x2	6/1/2016
x2	7/1/2016
x2	8/1/2016
x3	9/1/2016
x3	10/1/2016
x3	11/1/2016
x3	1/1/2016
x3	2/1/2016
x3	3/1/2016
x3	4/1/2016

Re: LOGIN DETAILS LAST 3 MONTHS DATA

sunny_talwar — Wed, 09 Nov 2016 12:35:58 GMT

May be like this:

Dimension

USERNAME

Expression

Count({<[LOGIN HISTORY] = {"$(='>' & Date(AddMonths(Max([LOGIN HISTORY]), -3), 'MM/DD/YYYY') & '<=' & Date(Max([LOGIN HISTORY]), 'MM/DD/YYYY'))"}>}USERNAME)

Re: LOGIN DETAILS LAST 3 MONTHS DATA

sunny_talwar — Wed, 09 Nov 2016 12:37:11 GMT

Or this if it is last 3 months from today

Count({<[LOGIN HISTORY] = {"$(='>' & Date(MonthStart(Today(), -3), 'MM/DD/YYYY') & '<=' & Date(MonthEnd(Today()), 'MM/DD/YYYY'))"}>}USERNAME)

Re: LOGIN DETAILS LAST 3 MONTHS DATA

manoranjan_d — Wed, 09 Nov 2016 12:41:43 GMT

can you provide me in the script level bcoz this expression will make performance issue?

Re: LOGIN DETAILS LAST 3 MONTHS DATA

sunny_talwar — Wed, 09 Nov 2016 12:43:47 GMT

You want to bring in only last three months of data?

Re: LOGIN DETAILS LAST 3 MONTHS DATA

manoranjan_d — Wed, 09 Nov 2016 12:47:20 GMT

yes in the front end application i want to show the last three months data

Re: LOGIN DETAILS LAST 3 MONTHS DATA

manoranjan_d — Wed, 09 Nov 2016 12:48:12 GMT

but same time i m using the master calendar in the script level

Re: LOGIN DETAILS LAST 3 MONTHS DATA

sunny_talwar — Wed, 09 Nov 2016 12:48:41 GMT

Then use a where statement

LOAD USERNAME,

[LOGIN HISTORY]

FROM ....

Where [LOGIN HISTORY] > AddMonths(Today(), -3);

Re: LOGIN DETAILS LAST 3 MONTHS DATA

manoranjan_d — Wed, 09 Nov 2016 12:56:11 GMT

can you pls provide me in the qvw file i want to check this

Re: LOGIN DETAILS LAST 3 MONTHS DATA

manoranjan_d — Thu, 10 Nov 2016 13:05:38 GMT

hi can we use this

=Count({< [LOGIN HISTORY] = {"$(='>' & Date(MonthStart(Today(), -3), 'MM/DD/YYYY') & '<=' & Date(MonthEnd(Today(),-1), 'MM/DD/YYYY'))"}>}USERNAME)

becoz ur monthend false on 30nov2016

i need from 1augto 31oct

so is this fie a?