i want to get the number of users added previous month (there may be more than 1 date for the same user since it keeps track of user login))

[Qlikuser09]

I want to get the number of users added the previous month (there may be more than 1 date for the same user but for different applications since it keeps track of user login))

if(Aggr(Min(Month([Access Date])), [UserID])= Month(AddMonths(Max(Date),-1)),1,0) this is not working

[anthonyj · Accepted solution]

Hi,

You mentioned that this table keeps track of user logins so I assume that a UserID can have a login record that is in the last month but also has records for previous months. If you only want to count the number of users in the last month you can do a count distinct on UserID when the Access Date was during last month.

eg.

count({$<[Access Date]={">=$(=monthstart(today(0),-1))<$(=monthstart(today(0)))"}>}distinct UserID)

As of 06/08/2021 this resolves to >=01/07/2021<01/08/2021.

If you want to count the the UserID's that have had their first Access Date in July 2021 then we need to exclude any UserID's that were seen before 01/07/2021:

count({$<[Access Date]={">=$(=monthstart(today(0),-1))<$(=monthstart(today(0)))"}, UserID=E({<[Access Date]={"<$(=monthstart(today(0),-1))"}>})>}distinct UserID)

The UserID=E( ) function creates a list of UserID's before 01/07/2021 and passes this to the set analysis as a list to exclude from the first set analysis.

Here's some test data to demonstrate what it is doing:

data:
load * Inline [
UserID Access Date
JID 5/08/2021
ABL 3/07/2021
DIM 06/08/2021
FLD 20/07/2021
KMW 29/08/2021
FRD 15/06/2021
FRD 18/07/2021
KFL 24/07/2021
MLD 17/06/2021
DIM 17/06/2021

](delimiter is '\t');

Regards

Anthony

[Qlikuser09]

Thanks a lot, it worked , if I want to drill down into for which application that the user got access(Column name: App name)

[Qlikuser09]

What if i want to check with another condition in the same script like (Allowed = ' A')

[anthonyj · Accepted solution]

Hi,

If you need to add another condition you can add like this:

count({$<[Access Date]={">=$(=monthstart(today(0),-1))<$(=monthstart(today(0)))"}, Allowed={'A'}, UserID=E({<[Access Date]={"<$(=monthstart(today(0),-1))"}, Allowed={'A'}>}UserID)>}distinct UserID)

You'll have to make a determination as to whether you want the 'Allowed' condition to be in both set analysis or not. Written as above will mean that it will count UserID's that have Allowed = 'A' and there hasn't been a previous month where they had a record where Allowed = 'A'.

If you want the count of UserID's where the first iteration of a record was last month and it was Allowed 'A' and that UserID has never been seen before then keep the Allowed = 'A' out of the E( ).

I hope this makes sense.

Regards

Anthony

[Qlikuser09]

How to get the customers that are not part of any sales in the last three months? Customers that are inactive for 3 months 

=Count({<[User ID]=E({<Date={">=$(=addmonths(max(Date),-3))<=$(=max(Date))"}>}[User ID])>}Distinct [User ID])

This is returning the customers by eliminating the users for the three months(April-July) and display results like the one listed below. As a result, I have non-distinct customer ID's well. I want distinct customers id's who don't have any date entry  in the last three months.

Customer ID	Purchase year month
1	2021-4
2	2021-4
2	2021-4
3	2021-3
4	2021-2
4	2021-2
5	2021-1
6	2021-1
7	2021-1
7	2018-5
8	2017-3
9	2020-7
10	2020-3
11	2021-1

[anthonyj]

Hi,

Qlik displays duplicates of rows in a table if there is a difference between the rows so having two identical rows of Customer ID and Purchase Year Month is odd. I guess the question I'd ask is:

Are these the only two columns in your table or is there another column that has different values forcing them apart.

Or maybe the underlying value of Purchase Year Month is a specific date.

I have had tables split values apart before because I've added columns from different source tables in the data model.

If it is just these two columns then you could replace your date dimension with the below function and see if it gets rid of the duplicate rows.

date(aggr(max([Purchase year month]), [Customer ID]))

Regards

Anthony

[Qlikuser09]

Yes, i am having another column named Item, is there a way I can group by the result with the items. such that the table displays the item that was not purchased by the customer in the last three months? 

[anthonyj · Accepted solution]

By the sounds of things your "Item" column" is duplicating your CustomerID and Purchase Month rows so try adding the Item column as a measure with the concatenation function:

concat(Item, ', ')

This will put all the items  they purchase on one row with comma separation. Then go to the "Totals Function" and select "None".

Regards

Anthony