No Aggregation on Table visual

hello all I am building a dashboard i have some calculated fields I am creating at the data level in QS. when I try to add those fields in QS UI in a table it tries to sum or do additional aggregations. I only want the actual values since calculations or aggregations have been applied at the data level already. Below are examples of the aggregations already applied at the data level.

– Calculated fields for negative adjustments and efficiency metrics
CASE WHEN op.TOT_AUX_CONSPTN_DELTA < 0 THEN 0 ELSE op.TOT_AUX_CONSPTN_DELTA END AS TOT_AUX_CONSPTN_DELTA,
CASE WHEN op.TOT_AUX_CONSPTN_STNDSTL_DELTA < 0 THEN 0 ELSE op.TOT_AUX_CONSPTN_STNDSTL_DELTA END AS TOT_AUX_CONSPTN_STNDSTL_DELTA,
CASE WHEN op.TOT_AUX_CONSPTN_MOVING_DELTA < 0 THEN 0 ELSE op.TOT_AUX_CONSPTN_MOVING_DELTA END AS TOT_AUX_CONSPTN_MOVING_DELTA,
CASE WHEN op.TOT_CONSPTN_NOT_PLUGGED_DELTA < 0 THEN 0 ELSE op.TOT_CONSPTN_NOT_PLUGGED_DELTA END AS TOT_CONSPTN_NOT_PLUGGED_DELTA,
CASE WHEN op.TOT_CHARGE_FAST_DELTA < 0 THEN 0 ELSE op.TOT_CHARGE_FAST_DELTA END AS TOT_CHARGE_FAST_DELTA,
CASE WHEN op.TOT_PROPULSION_CONSPTN_DELTA < 0 THEN 0 ELSE op.TOT_PROPULSION_CONSPTN_DELTA END AS TOT_PROPULSION_CONSPTN_DELTA,
CASE WHEN op.TOT_CHARGE_RECUP_DELTA < 0 THEN 0 ELSE op.TOT_CHARGE_RECUP_DELTA END AS TOT_CHARGE_RECUP_DELTA,
CASE WHEN op.TOT_FUEL_CONSPTN_DELTA < 0 THEN 0 ELSE op.TOT_FUEL_CONSPTN_DELTA END AS TOT_FUEL_CONSPTN_DELTA,
CASE WHEN op.ODOMETER_READING_DELTA < 0 THEN 0 ELSE op.ODOMETER_READING_DELTA END AS ODOMETER_READING_DELTA,
CASE WHEN op.TOT_PTO_ENERGY_STNDSTL_DELTA < 0 THEN 0 ELSE op.TOT_PTO_ENERGY_STNDSTL_DELTA END AS TOT_PTO_ENERGY_STNDSTL_DELTA,
CASE WHEN op.TOT_PTO_ENERGY_MOVING_DELTA < 0 THEN 0 ELSE op.TOT_PTO_ENERGY_MOVING_DELTA END AS TOT_PTO_ENERGY_MOVING_DELTA,
CASE WHEN op.TOT_AUX_TRACTN_CONSPTN_DELTA < 0 THEN 0 ELSE op.TOT_AUX_TRACTN_CONSPTN_DELTA END AS TOT_AUX_TRACTN_CONSPTN_DELTA,
CASE WHEN op.TOT_AUX_TRACTN_CONSPTN_STNDSTL_DELTA < 0 THEN 0 ELSE op.TOT_AUX_TRACTN_CONSPTN_STNDSTL_DELTA END AS TOT_AUX_TRACTN_CONSPTN_STNDSTL_DELTA,
CASE WHEN op.TOT_AUX_TRACTN_CONSPTN_MOVING_DELTA < 0 THEN 0 ELSE op.TOT_AUX_TRACTN_CONSPTN_MOVING_DELTA END AS TOT_AUX_TRACTN_CONSPTN_MOVING_DELTA,
CASE WHEN op.TOT_PEAK_POWER_DELTA < 0 THEN 0 ELSE op.TOT_PEAK_POWER_DELTA END AS TOT_PEAK_POWER_DELTA,
CASE WHEN op.TOT_CHARGE_SLOW_DELTA < 0 THEN 0 ELSE op.TOT_CHARGE_SLOW_DELTA END AS TOT_CHARGE_SLOW_DELTA,
CASE WHEN op.REMAINING_ENERGY_DELTA < 0 THEN 0 ELSE op.REMAINING_ENERGY_DELTA END AS REMAINING_ENERGY_DELTA,
CASE WHEN op.TOT_PEAK_DISTANCE_DELTA < 0 THEN 0 ELSE op.TOT_PEAK_DISTANCE_DELTA END AS TOT_PEAK_DISTANCE_DELTA,
CASE WHEN op.TOT_PEAK_TIME_DELTA < 0 THEN 0 ELSE op.TOT_PEAK_TIME_DELTA END AS TOT_PEAK_TIME_DELTA,
CASE WHEN op.TOT_DISTANCE_RECUP_DELTA < 0 THEN 0 ELSE op.TOT_DISTANCE_RECUP_DELTA END AS TOT_DISTANCE_RECUP_DELTA,
CASE WHEN op.TOT_TIME_RECUP_DELTA < 0 THEN 0 ELSE op.TOT_TIME_RECUP_DELTA END AS TOT_TIME_RECUP_DELTA,
CASE WHEN op.TIME_TO_VENT_DELTA < 0 THEN 0 ELSE op.TIME_TO_VENT_DELTA END AS TIME_TO_VENT_DELTA,
CASE WHEN op.TOT_PTO_TIME_STNDSTL_DELTA < 0 THEN 0 ELSE op.TOT_PTO_TIME_STNDSTL_DELTA END AS TOT_PTO_TIME_STNDSTL_DELTA,
– Total Energy Consumption and Energy Efficiency Calculations
(CASE WHEN op.TOT_PROPULSION_CONSPTN_DELTA < 0 THEN 0 ELSE op.TOT_PROPULSION_CONSPTN_DELTA END -
CASE WHEN op.TOT_CHARGE_RECUP_DELTA < 0 THEN 0 ELSE op.TOT_CHARGE_RECUP_DELTA END +
CASE WHEN op.TOT_AUX_CONSPTN_MOVING_DELTA < 0 THEN 0 ELSE op.TOT_AUX_CONSPTN_MOVING_DELTA END +
CASE WHEN op.TOT_AUX_CONSPTN_STNDSTL_DELTA < 0 THEN 0 ELSE op.TOT_AUX_CONSPTN_STNDSTL_DELTA END) AS Total_Energy_Consumption,
CASE
WHEN op.ODOMETER_READING_DELTA = 0 THEN NULL
ELSE (CASE WHEN op.TOT_PROPULSION_CONSPTN_DELTA < 0 THEN 0 ELSE op.TOT_PROPULSION_CONSPTN_DELTA END) / op.ODOMETER_READING_DELTA
END AS Energy_Efficiency_kWh_per_Mile,
CASE
WHEN (CASE WHEN op.TOT_PROPULSION_CONSPTN_DELTA < 0 THEN 0 ELSE op.TOT_PROPULSION_CONSPTN_DELTA END) = 0 THEN NULL
ELSE op.ODOMETER_READING_DELTA / (CASE WHEN op.TOT_PROPULSION_CONSPTN_DELTA < 0 THEN 0 ELSE op.TOT_PROPULSION_CONSPTN_DELTA END)
END AS Energy_Efficiency_Miles_per_kWh,
– Monthly summary and non-propulsion energy ratio
SUM(op.TOT_PROPULSION_CONSPTN_DELTA) OVER (PARTITION BY op.VIN, TRUNC(op.REPORT_TIME, ‘DD’)) AS Sum_Propulsion,
SUM(op.TOT_AUX_CONSPTN_MOVING_DELTA + op.TOT_AUX_CONSPTN_STNDSTL_DELTA) OVER (PARTITION BY op.VIN, TRUNC(op.REPORT_TIME, ‘DD’)) AS Sum_Non_Propulsion,
CASE
WHEN SUM(op.TOT_PROPULSION_CONSPTN_DELTA) OVER (PARTITION BY op.VIN, TRUNC(op.REPORT_TIME, ‘DD’)) <= 0 THEN NULL
ELSE SUM(op.TOT_AUX_CONSPTN_MOVING_DELTA + op.TOT_AUX_CONSPTN_STNDSTL_DELTA) OVER (PARTITION BY op.VIN, TRUNC(op.REPORT_TIME, ‘DD’)) /
SUM(op.TOT_PROPULSION_CONSPTN_DELTA) OVER (PARTITION BY op.VIN, TRUNC(op.REPORT_TIME, ‘DD’))
END AS Non_Propulsion_Energy_Ratio,
CASE
WHEN (CASE WHEN op.TOT_PROPULSION_CONSPTN_DELTA < 0 THEN 0 ELSE op.TOT_PROPULSION_CONSPTN_DELTA END) = 0 THEN NULL
ELSE (CASE WHEN op.TOT_CHARGE_RECUP_DELTA < 0 THEN 0 ELSE op.TOT_CHARGE_RECUP_DELTA END) /
(CASE WHEN op.TOT_PROPULSION_CONSPTN_DELTA < 0 THEN 0 ELSE op.TOT_PROPULSION_CONSPTN_DELTA END)
END AS Regen_Energy_Efficiency,
SUM(CASE WHEN op.ODOMETER_READING_DELTA >= 5 THEN 1 ELSE 0 END) OVER (
PARTITION BY op.VIN, TO_CHAR(op.REPORT_TIME, ‘YYYY’), TO_CHAR(op.REPORT_TIME, ‘IW’)
) AS Actual_Operating_Days,
SUM(CASE WHEN TO_CHAR(op.REPORT_TIME, ‘D’) BETWEEN ‘2’ AND ‘6’ THEN 1 ELSE 0 END) OVER (
PARTITION BY op.VIN, TO_CHAR(op.REPORT_TIME, ‘YYYY’), TO_CHAR(op.REPORT_TIME, ‘IW’)
) AS Target_Operating_Days,
SUM(CASE WHEN op.ODOMETER_READING_DELTA >= 5 THEN 1 ELSE 0 END) OVER (
PARTITION BY op.VIN, TO_CHAR(op.REPORT_TIME, ‘YYYY’), TO_CHAR(op.REPORT_TIME, ‘IW’)
) * 1.0 / NULLIF(
SUM(CASE WHEN TO_CHAR(op.REPORT_TIME, ‘D’) BETWEEN ‘2’ AND ‘6’ THEN 1 ELSE 0 END) OVER (
PARTITION BY op.VIN, TO_CHAR(op.REPORT_TIME, ‘YYYY’), TO_CHAR(op.REPORT_TIME, ‘IW’)
), 0
) AS Utilization_Rate,
CASE WHEN op.ODOMETER_READING_DELTA >= 5 THEN 1 ELSE 0 END AS Is_Actual_Operating_Day,
CASE WHEN op.TOT_CHARGE_FAST_DELTA > 0 THEN 1 ELSE 0 END AS Charging_Days, – Added Charging Days calculation

Hi @olusegun83,

If you put all your fields in the “Value” field well and don’t put anything in the “Group by” field well, there won’t be any aggregation. Can you try that?

thanks David, for your response. When i put in all the values in without anything to group by i get consistent or null values across all as seen in the screenshot. I tried adding a date then it automatically aggregates the values. I tried a workaround using the max function to get the actual values, but this not what I need. Please advice.



Hi @olusegun83,

To avoid aggregations being applied to the fields, you could try converting them to strings, for example:
I created a calculated field:
Profit per item
Profit/Quantity

Then another called
Profit per item as Text
toString({Profit per item})

Then using this calculated field in the values field well prevents aggregation, for example
image

Would a similar approach work by converting calculated fields to strings for display purposes?

Many Thanks,
Andrew