LinkedIn
Twitter
I have data in the below sample format (highlighted in blue).
I do not want a sum of all rows but only want to sum those rows where the Supplier is unique AND within each supplier only where the date is unique.(Column E)
Can someone suggest a calculated field to achieve this?
I would like to avoid having to amend the source data if possible.
Thanks
Solved
1 ACCEPTED SOLUTION
Hi,
To get this done, we’ll need to set up a secondary data source for reaggregation. First, we’ll work with the original data to identify two fields that, when combined, will give us a unique identifier for each row. Since your data has duplicates, we’ll take the minimum value for each unique combination.
The first calculated field we’ll create will be called (Supplier/Date Unique Key), feel free to name it whatever works for you: Supplier/Date Unique Key: CONCAT (Supplier, "_", Date)
This will combine the supplier and date into one string, giving us the unique key we need.
Now, for the next step, we want to make sure that each combination key returns only the minimum value for the ‘Room’ column. We’ll use this calculation, and call it Unique Room Aggregation (MIN):
Unique Room Aggregation (MIN):
CASE
WHEN COUNT_DISTINCT (Supplier/Date Unique Key) = 1 THEN MIN(Rooms)
ELSE 0
END
Once these basic calculations are set up, we can move forward. However, because Looker Studio doesn’t support reaggregation yet, we can’t just sum the Unique Room Aggregation (MIN) in a scorecard to get the results we want.
To work around this, we’ll create a secondary data source. This will allow us to extract the data, including the calculated fields, and treat them as record-level data. Here’s how:
Click on "Add Data" and select "Extract Data."
1. In the window that appears, choose the calculated fields (Supplier/Date Unique Key and Unique Room Aggregation (MIN)).
2. Drag and drop the dimensions and metrics where they belong.
3. On the right side, click the "Save and Extract" button.
Don’t forget to check the "Auto Update" box to have your data refresh automatically at set intervals. Once this is done, you’ll have a snapshot of your data, and you can start building your visualizations.
For the expected results, click the link below
https://lookerstudio.google.com/reporting/3fd6e1f4-fe6c-4bc3-974c-daf71d7d56a3/page/fmFDF
