Create a dimension that creates a field for last login year

[asinghal0412]

Dear Qlik community,

In a table, I have  2 dimensions, 'User ID' and 'Last login dt' (yyyy-mm-dd).

I want to create a 3rd dimension that has a list of 'UserID' whose 'last login dt' is more than 2 years ago.

I want to use set analysis to achieve this task.

thanks,

AS

[GaryGiles]

If you want to create a list of users who have not logged in in more that 2 years in a table chart object in Qlik Sense, you can use the following in a dimension expression:

=aggr(Only({$<[Last login dt]={"<=$(=Addmonths(Today(),-24))"}>} [User ID]), [User ID])

[asinghal0412]

Hi Gary,

Unfortunately, the expression generates all null values. 

A small mistake in the question - the format of 'Last login dt '(dd, mm,yyyy) should be (YYYY, MM, DD. I have changed it now. Do you think it will effect the expression?

[GaryGiles]

The date format could be a problem.  Try it with the Date() function, like this:

=aggr(Only({$<[Last login dt]={"<=$(=Date(Addmonths(Today(),-24),'YYYY,MM,DD'))"}>} [User ID]), [User ID])

[asinghal0412]

Also, there are many 'Last login dt' which corresponds to multiple 'User id', hence, in such a scenario, using Only() will result in 'Null' in the result list. Is my deduction correct? if yes, can you provide an alternative solution?

[asinghal0412]

The response is still 'NUll', I do understand the logic behind the code but do you think it is showing Null because we are using Only(). As the 'Last login dt' has multiple 'User id'.

[GaryGiles]

The Aggr() function will summarize the Only() expression for each User ID, so that will mean only 1 User ID will be returned for each.  

Create a table chart with [Last login dt] as a dimension and Date(Addmonths(Today(),-24),'YYYY,MM,DD') as another dimension.  Do the formats match?

[asinghal0412]

Hey Gary,

Thanks again for your support, much appreciated. I solved the issue with the code below! 

if(Avg(today() - Date#([FPL Last Login],'YYYY/MM/DD'))>730,[FPL UserID])

Best,

Akash