Multiple results encountered for the same location in Pivot Table indicated by ****

camdyn

Attached is a sample of my data. I am trying to create a pivot table with the following:

Rows: location, week, date, shift

Columns: count(unique identifier), count(unique identifier) * shift length

In my ETL, I created a formula to handle empty values. It determines whether a shift was 8 hours or 10 hours, based on a schedule ID field from a separate table. Here is the logic:

CASE
WHEN shift length IS NOT NULL THEN shift length
WHEN shift length IS NULL AND Schedule ID LIKE '%-08 %' THEN 8.0
WHEN shift length IS NULL AND Schedule ID LIKE '%-10 %' THEN 10.0
ELSE 10.0
END

When I try to create this pivot table, I get the error in the title. I've noticed that, when I convert my pivot table to a mega table, the issue is that 8 hour shifts and 10 hour shifts are not being grouped together. The error and the **** goes away when I add a column to separate 8 hour and 10 hour shifts, but I want one total given for each shift/day/location. What is wrong with my logic that is preventing this from happening?

Data Sample (1).xlsx

DavidChurchman

You need to aggregate shift length so the pivot table knows how to combine the cells. Wrap it in either a sum() or avg(), depending on which makes more sense for you for this case

I think you might need to wrap the 8s and 10s, too

camdyn

@DavidChurchman Thanks for the reply! When you say wrap the 8s and 10s, what do you mean by that? Also, I've tried doing COUNT(unique identifier) * SUM(shift length) in the analyzer and it did not fix my problem, but the ETL editor won't let me wrap my case statement in a SUM(). I'm getting stuck on how/when that aggregation should happen

DavidChurchman

I'm sorry, I didn't read your original post very carefully. Your formula in the ETL is fine so ignore my comment about wrapping the 8s and 10s.

Using your sample data, I could not recreate the error with your formulas (wrapping the shift length in a sum).

Re-reading, the only thing that might be wrong is your beastmodes should be your Values, not your columns.