How to sum the data of each row

[Paknanarn23]

I want to find the sum of the inputs separately from CU Base. CU Base has condition code like this.

=if(IsNull(PPMR_FinishedCUTotalActual),
if(PropertyName = 'mi3_BaseMaterial',Mid(ValueText, 7, 10)

// Trim(SubField(SubField(ValueText, ':', 2), 'Oz', 1))

// หรือสิ่งที่ต้องการให้เป็นค่า default เมื่อ PropertyName ไม่ใช่ 'mi3_BaseMaterial'
),
if(Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 10.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 20.00, '10-20',
if(Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 20.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 30.00, '20-30',
if(Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 30.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 40.00, '30-40',
if(Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 40.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 50.00, '40-50',
if(Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 50.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 60.00, '50-60',
if(Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 60.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 70.00, '60-70',
if(Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 80.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 90.00, '80-90',
if(Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 90.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 100.00, '90-100',
if(Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) > 100.00, '>100'

))))))))))

What should I do?

[TauseefKhan]

Hi @Paknanarn23,

Conditional Column:
DataWithConditions:
LOAD
*,
if(
IsNull(PPMR_FinishedCUTotalActual),
if(PropertyName = 'mi3_BaseMaterial', Mid(ValueText, 7, 10)),
if(
Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 10.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 20.00, '10-20',
if(
Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 20.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 30.00, '20-30',
if(
Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 30.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 40.00, '30-40',
if(
Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 40.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 50.00, '40-50',
if(
Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 50.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 60.00, '50-60',
if(
Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 60.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 70.00, '60-70',
if(
Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 80.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 90.00, '80-90',
if(
Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) >= 90.00 and Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) <= 100.00, '90-100',
if(
Aggr(Max(PPMR_FinishedCUTotalActual), WorkOrderNo_DF) > 100.00, '>100',
Null()
))))))))))) as CU_Base
RESIDENT YourData_Table;

sum according to the conditional categories create.visualizations using expressions such as:

Sum({<CU_Base={'10-20'}>} PPMR_FinishedCUTotalActual) // Summing data within '10-20' category

Sum({<CU_Base={'20-30'}>} PPMR_FinishedCUTotalActual) // Summing data within '20-30' category

// Add more such aggregations based on your needs

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

[Paknanarn23]

Hi,TauseefKhan

And if PPMR_FinishedCUTotalActual and PropertyName are in different tables What must I do?

[TauseefKhan]

Use of JOIN:

// Assuming 'Table1' has 'PPMR_FinishedCUTotalActual' and 'Table2' has 'PropertyName'
LEFT JOIN (Table1)
LOAD
WorkOrderNo_DF,
PropertyName,
ValueText
RESIDENT Table2;

DataWithConditions:
LOAD
*,
if(IsNull(PPMR_FinishedCUTotalActual),
if(PropertyName = 'mi3_BaseMaterial', Mid(ValueText, 7, 10)),
// Your existing nested if conditions
) as CU_Base
RESIDENT Table1;

Replace Table1 and Table2 with your actual table names.
The LEFT JOIN is used here to bring PropertyName and ValueText into the same table as PPMR_FinishedCUTotalActual before the LOAD statement that creates your conditional column.

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***