I am using looker to display merchandizing data from various stores. I have a table with the various SKU counts on the shelf. if I am looking at the data over a longer period (say 2 months) a single store would have been visited multiple times. I would like the data that is returned to be the most recent value, not the sum of all the values during the filtered period.
Can anyone assist with this?
Hi,
You can achieve this as follows. The two key fields we’re focusing on are data_visited and SKU_count. So, we’ll create two calculated fields to sort the data, first by the most recent date and then by the highest SKU count, both in descending order using the MAX() aggregation.
This ensures the data is sorted by the most recent date first and then by the latest SKU count.
Calculated Field 1:
MAX(data_visited)
Calculated Field 2:
MAX(SKU_count)
Once you’ve created these calculated fields, update your table accordingly. For sorting, since the most recent date takes priority over SKU count, make sure MAX(data_visited) is set as the primary sort and MAX(SKU_count) as the secondary sort.
You can check the expected output in the link below:
https://lookerstudio.google.com/reporting/7634498f-84d4-41f9-ad03-dc462a0fa6a8/page/9whDF
this isn’t exactly what I am looking for. This solution creates a lot more rows with empty data.
what I want is for only the most recent SKU value to be shown in the table.
Hi,
Thank you for responding. I will attach a picture to better explain what I am trying to do. I’m using a heat map to visualize the product counts. The larger numbers (10, 14 etc) are the sum of multiple visits since during that date range we would have gone to the stores multiple times.
How do I get the cell to return the result from the most recent visit rather than the sum. i.e. the cell should return the “last Value” which doesn’t seem to be a native function in looker studio
Hi Nikita2,
The picture should help get what you need, but before we dive in, let’s go over some default behaviours in Looker Studio:
With that in mind, let’s create a dummy dataset to simulate your scenario, where some product names have more than one visit and different SKU_Count values on the same date.
For example, on 2025-03-21, Product A had two visits with SKU_Counts of 5 and 4. Since we’re looking for the highest SKU_Count, the expected outcome should be 5 (the max value). You can check the pivot table on the right for reference.
Now, let’s connect to Looker Studio and get the same results.
In Looker Studio, we can achieve similar results using a pivot table with a Heatmap. To ensure the correct grain of data, we need to include products, visit dates, and SKU_Counts in the view. To set this up, we drag Date to the row dimension, Product Name to the column dimension, and SKU_Counts to the metric field. It is important to change the default aggregation from SUM to MAX to get the correct results.
https://lookerstudio.google.com/reporting/9cfd25b9-64c8-4d7e-8e17-ca63015392ef/page/n9PEF
