I have data consisting of ids for properties/stores and IP addresses associated with surveys. I have a count calculated field. It’s just a count({survey_id}) that gets a count of all surveys but changes based on filters. I’ve created pivot table with the property_id and ip_address as rows, reponse_month as column, and count as column. I have it set up with a filter that looks at data for count > 1. This allows me to see all the properties with repeating IP addresses. I also have a subtotal row that I created under “format visual”.
I’m trying to create a graph using the subtotal value. Essentially, this subtotal is the total number of surveys that have repeating IP addresses for each property.
How can I do this? Is there a way of creating a calculated field for this?
Hi @jp207, Are you trying to create a graph that shows survey count for each property id? If you remove the IP Address column from the pivot view you will be summarizing data at Property Id level and do you want to use that value for your chart/graph. It will be best if you can elaborate on your requirement with some examples.
@Karthik_Tharmarajan I have a filter setup that removes all counts that are less than one. However, because this just counts all surveys, what would happen when I remove the IP address column is that it will just filter for total survey counts per property instead of survey counts per property AND per IP address associated with each property. I want a subtotal of all surveys for each property where there is another survey with a matching IP address. I know it might sound a bit convoluted, so I hope I’m getting the point across properly.
Essentially, say a property with ID 20021 has surveys coming from IP addresses like 71.110.21.21 (1 survey), 172.291.21.20 (2 surveys), 32.21.32.12 (5 surveys). What I expect is subtotal of 7 because the IP addresses of 172.291.21.20 and 32.21.32.12 have more than one survey. I want the survey from 71.110.21.21 ignored because there’s only one single survey associated with it.
I can obviously see this properly on the pivot table, but I don’t know how to take those pivot table subtotal results and display them with a different visual (like a graph). This is why I was hoping there was some way of coming up with this subtotal using a calculated field that can be used for a graph.
@jp207, I am not sure how you are calculating survery count but I just gave it a try with below sample data. I did not use LAC and was able to get the total by excluding the count=1 value. Hope this helps. Sample Data
There is no actual “count” directly from the dataset. It’s calculated by doing count(). It’s the pivot table that’s really doing the count based on property_id and IP address. That actual data is a bit different. Let me post an example similar to what you did, but showing you what it looks like:
As you can see there is no actual count column. My count calculation is count(survey_id), but it doesn’t seem to matter what column I use for the count. When I do the pivot table setup, QuickSight is able to properly split the count based on IP addresses. It only works with the pivot table when “u_identifier” and “primary_id_address” columns are put as rows for it (u_identifier and primary_id_address are actually the names for property_id and ip_address columns in the real data set, I just gave those other names for readability sake when I originally made this post).
When I tried creating the surveycount column like you did, I do get the 0 value, but it does not give 0 for the subtotal. It counts all of the IP addresses with 1 survey for the subtotal. I have to go back to creating the count > 1 filter. I’m also not able to set aggregation as sum under the field wells section. It’s set as custom and I cannot change it.
