topic Re: Need expression for 1 year data in QlikView

Need expression for 1 year data

qliklearnervir — Wed, 26 Aug 2015 07:33:32 GMT

Hi,

My requirement is given below:

I have to show list of user , who have not logged in in the portal from 1 year from there last login .

fields are Login_Date, User_ID,User_name

please suggest .

Load Login_Date,User_ID,User_name

From (./Login.qvd)(qvd)

where Login_Date <= Today() and Login_Date >= Addyears(Today()-1)

NotExist(User_ID);

KIndly suggest how to do this equation.

Re: Need expression for 1 year data

Anonymous — Wed, 26 Aug 2015 07:42:23 GMT

I suggest the following order

1) load your login data

LogTable:

load

Login_Date,User_ID

From (./Login.qvd)(qvd)

where Login_Date <= Today() and Login_Date >= Addyears(Today()-1) ;

InActiveUser:

noconcatenate Load

User_ID,

User_Name

from (userdata.qvd)

where not exists(User_ID)

If you load your user earlier, than you need to rename the User_ID.

Re: Need expression for 1 year data

jagan — Wed, 26 Aug 2015 07:46:27 GMT

Hi,

Try like this

Load Login_Date,User_ID,User_name

From (./Login.qvd)(qvd)

where Login_Date <= Today() and Login_Date >= Addyears(Today(), -1);

This will load only those records which are in the last year from current date. i.e, 26-Aug-2104 to 26-Aug-2015.

If you have master table for users then you can load those users detials.

InActiveUser:

User_ID AS InActiveUserID,

User_Name AS InActiveUser

from (Users.qvd) (qvd)

WHERE NOT Exists(User_ID);

Now if you use below expression you will get the inactive count

=Count(Distinct InActiveUserID)

Hope this helps you.

Regards,

Jagan.

Re: Need expression for 1 year data

qliklearnervir — Wed, 26 Aug 2015 07:57:09 GMT

Hi,

there 1 more trick is there that i have to generate username and ids:

(1) track the calculation from user's last login date

(2) from last login date to till 1 year user don't have any login activity

means in between our range like <= TOday() and >=TOday()-1 , we have to find out those user list who have not login from there last login in this time period.

Re: Need expression for 1 year data

jagan — Wed, 26 Aug 2015 08:01:13 GMT

Hi,

If you attach sample file then it would be easier to understand the data and give the solution.

Qlik Community Tip: Posting Successful Discussion Threads

Check above link, it helps you in getting the answers faster.

Regards,

Jagan.