Grouped By Browsing (Pivot Tables)

Anyone familiar with Microsoft Excel will likely have used Pivot Tables., This functionality exists within InSite as Grouped By browsing. Rather than viewing each record within the data as a new row, Grouped By browsing allows you to group data by specific variables to view unique records and create subtotals based on a column’s contents. This guide will explain 3 different uses of this browsing, using sample data used within our training courses.

​EXAMPLE 1:

This data contains list of customers along with a column indicating the store code of the site that they shopped at. We can use the Group by option to split the list by this code and give a count of customers by store visited.

• Open a browse on your customer list, then right click and choose Add Column...

• Click on the Grouped box in the bottom right corner, below Selected Expressions

• Move theStore code column from your Selected Expressions to the Group By Expressions

• After doing this, the remaining columns will turn red (these cannot be aggregated by store code). Remove these red columns from your Selected Expressions

• Now type in the following expression to your expression box and then Add to your Selected Expressions:

count(*)

• This expression counts all the records within an aggregated set of records

• Click Ok

• You will now see that your browse has two columns: STORE (Store Code) and Customer Count (a count of customers who visited each store).

EXAMPLE 2:

In the same dataset, we also have Spend data and an occupation code identifier for each customer. Using this same method, we can create a table showing total spend by occupation.

• Open a browse on your customer list, then right click and choose Add Column...

• Click on the Grouped box in the bottom right corner, below Selected Expressions

• Move the Occupation Code column from your Selected Expressions to the Group By Expressions

• After doing this, the remaining columns will turn red (this is because these cannot be aggregated by store code). Remove these red columns from your Selected Expressions

• Now type the following in your expression box and Add it to your Selected Expressions: sum(MASTER.SPEND)

• This expression accumulates the spend values for all records within the grouped code

• Click Ok

• Your browse will have two columns: OCCUPATIONCODE (the various unique Occupation codes) and SPEND (the spend of all customers with that Occupation code)

EXAMPLE 3:

Find the number of customers that visited a specific store but also spent more than a certain amount (in this example £300)

• Open a browse on your customer list, then right click and choose Add Column...

• Click on the Grouped box in the bottom right corner, below Selected Expressions

• Move the Store code column from your Selected Expressions to the Group By Expressions

• After doing this, the remaining columns will turn red (these cannot be aggregated). Remove these red columns from your Selected Expressions

• Now add the following to your expression box and add it to your Selected Expressions:

  count(*)

• To get the count of customers that have spent more than x amount, we need to add another column. Add the following expression into the expression box:

  sum(INT( MASTER.SPEND > 300 ))

  • This expression works in the same way as Paste Count and the expression within the INT() function can be modified to suit (i.e. the expression counts each record within the dataset in which the bracketed expression is true)

  • You can change the above example £300 value to whatever value you want to check against

• Add this to your Selected Expressions and click Ok

• Your browse will have three columns: Branch Visited (the code of the store visited), Count of Customers (the number of customers who visited that store) and Count of Customers Spending More Than £300 (customers who visited that store and met the spend criteria)