Hello
I am trying to create a calculate field in my data sete but i get error messagge: “We encountered an error trying to save your calculated field. Please try creating it again.” Any hint to fix it?
Already tried to
- delete and create again
- split the formula in 2 different calculated field (calculate the sumover in a dedicated calculated field and then create a another calculated field with the division for the current value)
This is the formula of calculated field:
{Value} / sumOver(sum({Value}), [{Site}, {Week}, {Section}])
I need this calculated field to be inserted in my dataset to be published in an analysis with pivot table.
GOAL: have in my pivot table (for each section (and i have several) the % value ( value of current row / sum of the section according different criteria)
This is a test sample of data in my data set
| Site |
Section |
Item |
Month |
Week |
Value |
| Site1 |
1. Labor |
MCM NET |
2. February |
04/02/2024 |
20.00 |
| Site1 |
1. Labor |
MCM EUE |
2. February |
04/02/2024 |
10.00 |
| Site2 |
1. Labor |
MCM NET |
2. February |
04/02/2024 |
5.00 |
| Site2 |
1. Labor |
MCM EUE |
2. February |
04/02/2024 |
4.00 |
| Site3 |
1. Labor |
MCM NET |
2. February |
04/02/2024 |
30.00 |
| Site3 |
1. Labor |
MCM EUE |
2. February |
04/02/2024 |
7.00 |
| Site1 |
1. Labor |
MCM NET |
2. February |
11/02/2024 |
15.00 |
| Site1 |
1. Labor |
MCM EUE |
2. February |
11/02/2024 |
6.00 |
| Site2 |
1. Labor |
MCM NET |
2. February |
11/02/2024 |
7.00 |
| Site2 |
1. Labor |
MCM EUE |
2. February |
11/02/2024 |
8.00 |
| Site3 |
1. Labor |
MCM NET |
2. February |
11/02/2024 |
42.00 |
| Site3 |
1. Labor |
MCM EUE |
2. February |
11/02/2024 |
31.00 |
| Site1 |
1. Labor |
MCM NET |
3. March |
03/03/2024 |
5.00 |
| Site1 |
1. Labor |
MCM EUE |
3. March |
03/03/2024 |
7.00 |
| Site2 |
1. Labor |
MCM NET |
3. March |
03/03/2024 |
12.00 |
| Site2 |
1. Labor |
MCM EUE |
3. March |
03/03/2024 |
15.00 |
| Site3 |
1. Labor |
MCM NET |
3. March |
03/03/2024 |
2.00 |
| Site3 |
1. Labor |
MCM EUE |
3. March |
03/03/2024 |
5.00 |
| Site1 |
2. Allocated |
Ticket EUE |
2. February |
04/02/2024 |
80.00 |
| Site1 |
2. Allocated |
Ticket Net |
2. February |
04/02/2024 |
19.74 |
| Site2 |
2. Allocated |
Ticket EUE |
2. February |
04/02/2024 |
60.00 |
| Site2 |
2. Allocated |
Ticket Net |
2. February |
04/02/2024 |
114.37 |
Hi @castalm Thanks for reaching out, and welcome to the QuickSight Community! 
I am pinging one of our SAs to make sure your question gets a reply on Monday if we don’t hear back from the community before then. Thx!
1 Like
@castalm ,
The formula created is not valid , look into limitations of LAC-A : Using level-aware calculations in Amazon QuickSight - Amazon QuickSight
Please use the Arena to load the sample data. Can you then provide an example based on the data of end result what you trying to achieve ?
For eg If we take the combination [{Site}, {Week}, {Section}] ( Site1, 2.Allocated, 04/02/2024 ) .
Are you calculating the for row (Site1/2. Allocated Ticket EUE/2. February/04/02/2024) = 80 / total ( 80+19.74+60+114.37 ) ?
1 Like
Hello!
I have addressed the issue related to the formula insertion by:
- create a new analysis
- inserting the formula in VALUES measure (before it was in a CALCULATED FIELD itself)
now the formula is accepted but calculations are wrong.
Now the formula for VALUE is:
ifelse(${pDimensionMETRICS}=‘Hours’, sum(Value),
${pDimensionMETRICS}=‘%’, sumOver(sum(Value), [${pDimensionSCOPE}, ${pDimensionTIME}, Section, Item]) / sumOver(sum(Value), [${pDimensionSCOPE}, ${pDimensionTIME}, Section]) * 100 ,
NULL)
where
pDimensionSCOPE is parameter for Site or Country(new column)
pDimensionTIME is paramter for Week or Month
The formula with static value returns correct calculation:
ifelse(${pDimensionMETRICS}=‘Hours’, sum(Value),
${pDimensionMETRICS}=‘%’, sumOver(sum(Value), Site, Week, Section, Item]) / sumOver(sum(Value), Site, Week, Section]) * 100 ,
NULL)
Any idea why the calculation are wrong?
The issue was related to:
- data aggregation - numerator and denominator were aggregate in different moment
- mix of LAC-A AND LAC-W that prevent comparison between calculation
Formula that fix my issue:
ifelse(${pDimensionMETRICS}=‘Hours’, Value,
${pDimensionMETRICS}=‘%’,
ifelse (${pDimensionSCOPE}='Site' and ${pDimensionTIME}='Week', (Value / {DEN week-site}) * 100 ,
${pDimensionSCOPE}='Site' and ${pDimensionTIME}='Month', (Value / {DEN month-site}) * 100 ,
${pDimensionSCOPE}='Node' and ${pDimensionTIME}='Week', (Value / {DEN week-node}) * 100 ,
${pDimensionSCOPE}='Node' and ${pDimensionTIME}='Month', (Value / {DEN month-node}) * 100 ,
${pDimensionSCOPE}='Country' and ${pDimensionTIME}='Week', (Value / {DEN week-country}) * 100 ,
${pDimensionSCOPE}='Country' and ${pDimensionTIME}='Month', (Value / {DEN month-country}) * 100 ,
NULL),
NULL)
where {DEN X-Y} is a calculated field with this formula: sumOver(Value, [Y, Section, X], PRE_AGG)
I found really useful these links:
1 Like
Thank you for circling back and sharing this solution @castalm. Super helpful to the Community!