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
