How to find the average of a calculated field

Q&A
, , ,
21

Hi, @David_Wong Thanks for your reply,

image
image1355×827 63.5 KB

As you can see here, the daily difference field is the total production and the daily production value is different from it. Here the min-test is a field that shows the hourly production rate.

I am creating a dashboard for my customers, where they can see the hourly production rate for 24 hours and also they can see the daily report for a specific period based on their start day and end day control selections.

image
image978×602 70.5 KB

Now in this table, TMST is set to hourly and it is showing the hourly production rate in the min-test field and the value is again different from the daily difference.

If I export the values into a excel sheet,

sum of min-test = 255460
sum of daily-difference = 212935

Now if I aggregate the TMST into minute format to get the MaxGCNT and MinGCNT for that day

image
image1480×385 52.1 KB

total production = MaxGCNT - MinGCNT
total production = 44503237 - 44249960 = 253277

So by using the periodOverPeriodDifference, I am getting almost accurate values. Initially, I also used MaxGCNT and MINGCNT for my calculations, but it’s not accurate for the 24-hour hourly calculations.

1 Like
22

Hello @Harsheena, I believe I figured out the best way to get the Daily_Production average over the course of the week. This function might vary slightly depending on how many days you want to average on (I am assuming a Mon-Fri work week), so you may need to make minor alterations.

This function toString(extract('WD',{Date Field}) is going to return the week date as a String number of 1 - 7. 1 is Sunday, 7 is Saturday.

Weekly_Avg = ifelse(toString(extract('WD',{Date Field})) = '6', periodOverPeriodDifference({Daily_MaxGCNT},TMST, DAY, 5)/5, NULL)

If you have any issues with that, you can try returning periodOverPeriodDifference({Daily_MaxGCNT},TMST, DAY, 5) in the if statement, then divide that value by 5 in another calculated field. I believe the first function will work as expected though and will return the average Daily_Production value for each week.

1 Like