cancel
Showing results for
Did you mean:

# Dashboard expression for 2 events happening in time period

9 - Travel Pro

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?

1 ACCEPTED SOLUTION
13 - Data Warehouse

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

4 REPLIES 4
13 - Data Warehouse

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

9 - Travel Pro

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.

13 - Data Warehouse

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

9 - Travel Pro

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!

Community Toolbox

Developers Group:

Product Feedback Forum: