Dashboard expression for 2 events happening in time period

[mattmca]

Hello,

I've been struggling to get a dashboard expression to work correctly. I have daily data for customer visits to a website. In this data, I have a column called "UserFirstLogin" which has a value of 0 or 1 and indicates whether that day is the first day a user has logged in, and "UserInvited" which has a value of 0 or 1 and indicates whether that day is the day a user was invited to join the website.

What I'm trying to get is an indicator widget that will give a count of the number of users in the intersection between UserFirstLogin = 1 and UserInvited = 1 for any time period that the dashboard user filters by. So, for example, if they filter by the current month, I want the indicator to show the total number of users that have UserFirstLogin = 1 and UserInvited = 1 anywhere in that given month.

The expression I tried using was (COUNT(UserID), Filter: UserInvited = 1, Filter: UserFirstLogin = 1). Because the data is daily, though, this ends up only giving me the total number of users who were both invited and logged in on the same DAY in the month rather than for the entire time period that the dashboard user is filtering by. Any suggestions on how to accomplish something like this?

[harikm007 · Accepted solution]

Hello @mattmca ,

Check whether this formula gives you expected result:

sum([UserID], 
    case 
    when max([UserInvited]) = 1 and max([UserFirstLogin]) = 1
    	then 1
    	else 0
    end)

-Hari

[mattmca]

Hi Hari,

Unfortunately, this gives me the same result as the initial approach I took, where it only seems to be counting individual days that have both MAX([UserInvited] = 1 and MAX([UserFirstLogin]) = 1, rather than looking at all records for a given user in the filtered time period.

[harikm007]

Hi @mattmca ,

I'm just checking to see whether I'm understanding the problem correctly - 

Is your dataset in below format?

When I apply the formula in this dataset I'm getting 3. 

-Hari

[mattmca]

Hi Hari,

Thank you for your help. You were correct in how my dataset is set up. I set up a new dataset to exactly match your sample data and I also got the correct answer of 3 (for January). This led me to dig into why my other dataset was not giving me the correct number. As it turns out, there was something weird happening with the data type of my alphanumeric UserID, and so it wasn't grouping the UserIDs together correctly, which is why my answer was off. I got the UserID fixed, and now I'm getting the results I should be. Thank you!