topic ? in QlikView

?

xyz1 — Sun, 14 Apr 2019 13:51:14 GMT

Re: how to count values whose interval difference is two months?

jheasley — Mon, 06 Mar 2017 11:32:12 GMT

Have you tried updating the number format on your field to 'MM hh:mm:ss'

Re: how to count values whose interval difference is two months?

maxgro — Mon, 06 Mar 2017 12:15:15 GMT

Using your 2 rows of test data

Logon:

load * Inline [

Account , Last Logon , Last login Time, Interval difference

0109222J , 6/18/2015 12:55:40 PM , 2/21/2017 6:34:59 PM , 614(days) or 20.467 Month

0109227R , 2/26/2017 1:00:15 PM , 2/27/2017 1:00:15 PM , 1(day) or 0.033 Month

];

Final:

load

deltamonths > 2 as flag

;

load

Account,

[Last Logon],

[Last login Time],

Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as t1,

Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as delta,

Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt')) as deltadays,

Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 as deltamonths

Resident Logon;

DROP Table Logon;

?

xyz1 — Sun, 14 Apr 2019 13:52:16 GMT

Re: how to count values whose interval difference is two months?

maxgro — Mon, 06 Mar 2017 12:44:22 GMT

try to add a where filter on your data

maybe you have to change the format code, orange

.....

FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE

Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2

;

?

xyz1 — Sun, 14 Apr 2019 13:52:52 GMT

Re: how to count values whose interval difference is two months?

maxgro — Mon, 06 Mar 2017 13:04:30 GMT

LOAD

"Pre-W2K Name", (Account)

if ("Pre-W2K Name" like 'User*' , 'USER',

if("Pre-W2K Name" like 'ADM*' , 'ADM',

if("Pre-W2K Name" like 'BAL*' , 'BAL',

if("Pre-W2K Name" like 'GEN*' , 'GEN',

if("Pre-W2K Name" like 'VC*' , 'VC',

if("Pre-W2K Name" like 'ADP*' , 'ADP',

if("Pre-W2K Name" like 'ROOM_*' , 'Room',

if("Pre-W2K Name" like 'FORMATION*' , 'FORMATION',

if("Pre-W2K Name" like 'SVC*', 'SVC'))))))))) as ACCOUNTGROUP,

if("Role" like 'DISABLED' , 'DISABLED',

if("Role" like 'DISABLED\NO_PWDEXP' , 'Disabled and Expired Password',

if("Role" like 'DISABLED\NO_PWDEXP\NO_PWDREQD' , 'Disabled, Password Expired and no password required',

if("Role" like 'NO_PWDEXP' , 'No Password Expired',

if("Role" like 'NO_PWDEXP\ENCRYPT' , 'Encrypted',

if("Role" like 'NO_PWDEXP\NO_PWDREQD' , 'No Password',

if("Role" like 'NO_PWDEXP\SMART' , 'Smart',

if("Role" like 'NO_PWDEXP\DELEGATE', 'Delegated')))))))) as ACCOUNTSTATUS,

"Display Name",

"Last Logon",

"Exhange Alias",

"E-Mail",

"Lync User",

Role,

DN,

City,

Today(),

TextBetween( DN, 'CN=', ',') as [TypeName],

TextBetween( DN, 'OU=', ',') as [SiteName],

TextBetween( DN, 'DC=', ',') as DC,

"Last login Time",

Status

FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE

Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2

;

?

xyz1 — Sun, 14 Apr 2019 13:53:25 GMT