Active users against user profiles

[awigham]

Hi all,

I have 2 tables: one showing user accounts, and one an audit log showing logins/account creations etc.

I want to be able to show the number of active users (from the audit log) as a % of the number of user profiles by month and client. I think I may need to create use Canonical dates but this is well beyond my current ability!

Does anyone have any suggestions?

[Moiz]

Hello,

How do you identify that the user is active ? & Do both the tables have Date Field ?

You can use Set Analysis to show the % of active users simply as :

Aggr(Count({<" Status Flag"={"Active"}>}distinct Users)/Count (Distinct Total Users),Client, Month)

Status Flag is the Field that tells us if the user is active or inactive.

[awigham]

The user is active if they appear on the audit log. If they don't they're not active.

Model is here:

[Moiz]

hello,

I think the active users should be calculated using event_type field .

The attached qvf might help you.

Regards,

Moiz

[awigham]

Thanks.

Can't seem to get correct results from that. If I add client and date filter panes it is telling me that 75% of users for Client001 were active in August and Sep.

The data has 4 active events for client 1: 2 in Aug and 2 in Sep which are from 2 users, EMail001 and Email008.

id	access_date	access_time	user_affected_id	user_affected_email	event_type
386007	01/08/2019	29:37.3	17800	Email001	Login - success
386058	01/09/2019	43:12.0	17373	Email001	Login - success
386011	01/08/2019	57:21.7	17373	Email008	Login - success
386057	01/09/2019	42:44.6	17373	Email008	Log out

In Aug, Client001 had 9 users, and in Sept they had 20 users. So in Aug 2/9 users were active (22%) and in Sep 2/20 (10%)