You’re offline. This is a read only version of the page.
372
Hi,
I have a Recap SOW database that contains the processing time and process name for each product. In the SP List used to submit data, there is only 1 recent history data, which contains the processing time and process name for each product. I want to calculate the percentage of product progress based on the serial number that has been passed.
For example, on the SP List:
product name = XXXX
process name = bsjadkabdlas
processing time = 3 hours
process sequence number = 90
whereas in the SOW Recap Database, product XXXX has 7 process names containing the sequence 'process serial number'.
Example:
serial number 56, the process name is kdjkafl, the processing time is 1 hour
serial number 60, the name of the process is doafjap, the processing time is 3 hours
serial number 70, process name is gsfada, processing time is 1.5 hours
serial number 80, process name is dadasd, processing time is 2 hours
serial number 84, the name of the process is seara, the processing time is 1 hour
serial number 90, process name is afadcx, processing time is 3 hours
serial number 95, process name is adwsxda, processing time is 2.5 hours
then, progress will show the number 60.7% obtained from the sum of the time based on the serial number of the product process before serial number 90.
If the process continues to serial number 95, then the percentage becomes 82.1%.
How to do it? Thank you.
Ok, the problem know is, the value of
gloProgressPercentage
is same for all PO numbers😔
OK let's check each part of the formula and analyze any potential issues:
Step 1: You're retrieving the Serial Number from the PergerakanKomponen list where the 'SO/PO/WBS' column matches SOPOWBS_Monitor.Text. You're then retrieving the 'No Sec SOW' field.
Step 2: You're filtering the AllRecapSOW list to get processes that match 'No sec SOW' less than or equal to the retrieved Serial Number and where '# No. PO' matches SOPOWBS_Monitor.Text.
Step 3: You're summing the Hours column from the filtered processes.
Step 4: You're summing the Hours column for all processes that match '# No. PO' with SOPOWBS_Monitor.Text.
Step 5: You're calculating the progress percentage as the ratio of total time passed to the overall processing time.
Everything seems logically sound, but there might be some potential areas that could lead to issues:
Data Types: Ensure that the columns such as 'No Sec SOW', 'No sec SOW', Hours are of the correct data types. The serial numbers should be numbers, and the Hours should be numerical values representing time.
Case Sensitivity: Ensure that the column names are written exactly as they appear in your database. The formula refers to both 'No Sec SOW' and 'No sec SOW' - note the difference in capitalization. If these are intended to refer to the same column, they should be written identically.
I assume this is just a typo and you only made the typo in this post and it's not actually in the formula like that, but please go ahead and double-check it anyway just in case.
Control Values: Check the value of SOPOWBS_Monitor.Text. Make sure that this control contains the correct value at the time the formula is evaluated.
Division by Zero: If gloTotalTimeOverall is zero, you would be dividing by zero in Step 5. You might want to add a condition to handle this case.
Example to change formula to handle division by zero:
Share
Report
All responses (
Answers (
// Step 5: Calculate the Progress
If(
gloTotalTimeOverall = 0,
Set(gloProgressPercentage, 0), // or any other value or action you want to assign in case of zero total time
Set(gloProgressPercentage, (gloTotalTimePassed / gloTotalTimeOverall) * 100)
)
Hello @poweractivate ,
I set that formula in OnVisible, then take this variable
gloProgressPercentage
to my label in gallery,
but show the same value. I adjust this formula with my database:
// Step 1: Retrieve the Serial Number from the SP List
Set(gloCurrentSerialNumber, LookUp(PergerakanKomponen, 'SO/PO/WBS' = SOPOWBS_Monitor.Text).'No Sec SOW');
// Step 2: Filter the SOW Recap Database
ClearCollect(colFilteredProcesses, Filter(AllRecapSOW, '# No. PO' = SOPOWBS_Monitor.Text && 'No sec SOW' <= gloCurrentSerialNumber));
// Step 3: Calculate the Total Processing Time
Set(gloTotalTimePassed, Sum(colFilteredProcesses, Hours));
// Step 4: Calculate the Overall Processing Time
Set(gloTotalTimeOverall, Sum(Filter(AllRecapSOW, '# No. PO' = SOPOWBS_Monitor.Text), Hours));
// Step 5: Calculate the Progress
Set(gloProgressPercentage, (gloTotalTimePassed / gloTotalTimeOverall) * 100)
SO/PO/WBS = No PO
can you help me check my formula? thank you
// Step 1: Retrieve the Serial Number from the SP List
Set(gloCurrentSerialNumber, LookUp(SPList, productName = "XXXX").processSequenceNumber);
// Step 2: Filter the SOW Recap Database
ClearCollect(colFilteredProcesses, Filter(SOWRecapDB, productName = "XXXX" && serialNumber <= gloCurrentSerialNumber));
// Step 3: Calculate the Total Processing Time
Set(gloTotalTimePassed, Sum(colFilteredProcesses, processingTime));
// Step 4: Calculate the Overall Processing Time
Set(gloTotalTimeOverall, Sum(Filter(SOWRecapDB, productName = "XXXX"), processingTime));
// Step 5: Calculate the Progress
Set(gloProgressPercentage, (gloTotalTimePassed / gloTotalTimeOverall) * 100);
You may consider above general approach see if it helps for you @firda59
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
