Help with Calculated Field

[Beginner]

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

[lauratilton]

So there would only ever be one row for each Supplier-Date combination?

If you use your current source with fields Supplier, Date, Rooms in a Looker Studio dashboard, you can create a table to achieve this without any calculated fields. Add Supplier and Date as dimensions, and Rooms as a metric with AVG aggregation, then another metric of Rooms with CT aggregation (maybe re-named "Count"). You'll get a table that has each unique Supplier and Date combination as rows, then a column that has the Rooms, then a column that shows the Count of rows for that Supplier and Date. Will that work for what you need?

[Beginner]

Hi,

In the source data there would be more than one row of each supplier -date combination but I want to include only one instance of that combination in the Desired Result (the rest should be considered as duplicate).

I wanted to have the results as a scorecard hence thought a calculated field might be best. The table you suggest does generate the correct subtotals (ie 33, 33 and 20) but how would I get the total of 86 into a scorecard?

[tawfikypc2017]

Hi there

I propose the following solution:

Step 1: Create a Calculated Field for Unique Identification

• Define a calculated field named SupplierDateID using the following formula:

SupplierDateID: CONCAT(Supplier, "_", Date)

This field will create a unique identifier for each Supplier and Date combination.

Step 2: Implement Aggregation and Running Sum in a Table

• Add a table to your report and configure the following:
• Dimensions: Include the necessary dimensions (e.g., Supplier, Date).
• Metrics: Add the "Rooms" metric.
• Aggregation: In the "Rooms" metric settings, select "Max" as the aggregation method.
• Running Calculation: Within the "Rooms" metric settings, choose "Running sum" as the running calculation.
• The table will then display a cumulative total, with the final value representing the desired sum of unique Supplier/Date combinations. In your example, this total is 86, as indicated in the attached image

[Sichali1 · 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

[Beginner]

Thanks Sichali1,

This is what I was looking for. Your step by step explanation was very helpful - I was not even aware of the extract function!

Thanks for taking the time to consider my problem.