Hi -
I have attempted building a Set Analysis to accomplish the scenario below, but cannot seem to figure it out. This is sample data I made up for this question, but represents what the end user is asking.
I have two data sets in my App. The first is a summary table with the total number of possible users for a program:
| Table2.Program_Name | Table2.Total_Possible_Users |
| A | 40 |
| B | 30 |
| C | 25 |
The second table is the number of people that have used the program each day:
| Table1.Program_Name | Table1.Date | Table1.Number_of_Logins |
| A | 8/1/2021 | 20 |
| B | 8/1/2021 | 30 |
| C | 8/1/2021 | 15 |
| A | 8/2/2021 | 10 |
| B | 8/2/2021 | 5 |
| C | 8/2/2021 | 20 |
| A | 8/3/2021 | 5 |
| B | 8/3/2021 | 10 |
| C | 8/3/2021 | 20 |
| A | 8/4/2021 | 10 |
| B | 8/4/2021 | 10 |
| C | 8/4/2021 | 10 |
I am attempting to build a summary table that shows the total number of logins by Program Name based on the date range selected by the end user, and regardless of this date range, show the number of possible users in another column.
So if I select all dates it would look like this:
| Program_Name | Number_of_Logins | Total_Possible_Users |
| A | 45 | 40 |
| B | 55 | 30 |
| C | 65 | 25 |
If I select only 8/1 and 8/2 it would look like this:
| Program_Name | Number_of_Logins | Total_Possible_Users |
| A | 30 | 40 |
| B | 35 | 30 |
| C | 35 | 25 |
So regardless of the date range, Total_Possible_Users is always the same.
I know if I do a straight join I get a many to one set, and the numbers in Total_Possible_User column is overstated.
I attempted to create a Set Analysis where Table1.Program_Name = Table2.Program_Name and get a Distinct Total_Possible_Users number. In all my attempts I either get 0 for each row or 95.
Any help or guidance is greatly appreciated.
