Count Progress

(1) Share

Report

Posted on by firda59

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@firda59

// 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

Was this reply helpful? Yes No
firda59 372 on at

Like (1)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@firda59

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:

// 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) )

Was this reply helpful? Yes No
firda59 372 on at

Like (0)

Report
Copy link

Link copied!
@poweractivate

Ok, the problem know is, the value of
gloProgressPercentage
is same for all PO numbers😔

Was this reply helpful? Yes No