I'm trying to develop a reach and frequency report by territory in Quicksight. For example, a rep may have 10 customers that the need to visit 3 times in Q1. Looking for a way to show this reach for each rep. The issue is that someone my have visited a customer 4 times, so simply looking at a total of the visits would be inflated.
I tried developing a custom ifelse field to say if a count was > 3, put the output as 3. This worked on individual customer rows. However, when collapsing the data to look at a territory summary, the custom field applies to the subtotal and therefore does not work.
Here is what happening when I use this formula for the Adjusted Visits column ifelse(Current Visits < 4,Current Visits,3)
+-----------+---------+---------------+----------------+-----------------+---------+
| Territory | Account | Visits Needed | Current Visits | Adjusted Visits | Reach % |
+-----------+---------+---------------+----------------+-----------------+---------+
| 99999 | Smith | 3 | 4 | 3 | 100 |
+-----------+---------+---------------+----------------+-----------------+---------+
| 99999 | Jones | 3 | 3 | 3 | 100% |
+-----------+---------+---------------+----------------+-----------------+---------+
| 99999 | Max | 3 | 3 | 3 | 100% |
+-----------+---------+---------------+----------------+-----------------+---------+
| SUBTOTAL | | 9 | 10 | 3 | 33% |
+-----------+---------+---------------+----------------+-----------------+---------+
What I am looking for would be the following (essentially the subtotal ignoring the formula)
+-----------+---------+---------------+----------------+-----------------+---------+
| Territory | Account | Visits Needed | Current Visits | Adjusted Visits | Reach % |
+-----------+---------+---------------+----------------+-----------------+---------+
| 99999 | Smith | 3 | 4 | 3 | 100 |
+-----------+---------+---------------+----------------+-----------------+---------+
| 99999 | Jones | 3 | 3 | 3 | 100% |
+-----------+---------+---------------+----------------+-----------------+---------+
| 99999 | Max | 3 | 3 | 3 | 100% |
+-----------+---------+---------------+----------------+-----------------+---------+
| SUBTOTAL | | 9 | 10 | 9 | 100% |
+-----------+---------+---------------+----------------+-----------------+---------+
