Forum Discussion

mattmca's avatar
mattmca
Cloud Apps
01-26-2022
Solved

Dashboard expression for 2 events happening in time period

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?

dashboards & reporting
  • harikm007's avatar
    harikm007
    01-27-2022

    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

Replies have been turned off for this discussion
  • harikm007's avatar
    harikm007
    Data Warehouse
    01-27-2022

    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's avatar
      mattmca
      Cloud Apps
      01-27-2022

      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's avatar
        harikm007
        Data Warehouse
        01-27-2022

        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