# How to find the average of a calculated field

Hi There,

I would like to find an average value for the daily production count by summing up values in the bar chart, How can I find it? I tried running avg, avgover and unfortunately, I get errors, any help would be greatly appreciated.

Hi @Harsheena,

Have you taken a look at this solution on the QuickSight Community Page?

Let me know if that doesn’t work and we can try something else.

Best,

Bhasi

1 Like

I tried the same formula but I got this error "1
sumOver({Daily_Production},[{TMST}],PRE_AGG)/distinctCountOver({Daily_Production},[{TMST}],PRE_AGG)
Execution order mismatch: PRE_FILTER calculations can’t have PRE_Agg operands.
I tried some other solutions posted in the community as well, but maybe I am missing something.

@Bhasi_Mehta Hi, Any help would be really appreciated in finding the average of daily production value?

Hi @Harsheena,

Are Daily_Production and/or TMST calculated fields? If so, can you show the calculations?

1 Like

@David_Wong TMST is my time field and Daily production is the calculated field.

Daily_MaxGCNT = periodToDatemaxOverTime(max(GCNT), TMST, DAY)

Daily_Production = periodOverPeriodDifference({Daily_MaxGCNT},TMST, DAY, 1)

where GCNT is my counter value

I tried this way but it gives me in accurate values as first record is missing.

testCount = periodToDateCountOverTime(count(extract(‘DD’,TMST)), TMST, MONTH)

last_Count = lastValue(testCount,[TMST ASC])

running = sumOver(Daily_Production )

Daily_Avg = running / {last_Count}

These are my calculations to find the average value but it gives inaccurate because of the missed first daily production record.

Maybe there is some other best way to find the average of the daily production, but when I tried with level aware calculations I always got errors, that is why I tried this way.

Any help would be greatly appreciated. Thanks in advance

Thanks

Hi @Harsheena,

Can you show a few rows of your raw data, including your GCNT field so that I can understand the granularity of the data? What does each row of your dataset represent?

Hi @David_Wong, Actually my device is an IoT-enabled embedded device, from my device every 3 minutes or based on events data is stored in DB, and GCNT is my global counter value and it will increase with respect to the time. This is my main field to calculate the print count, production rate, etc.

Here for the device UID 2875, the GCNT value is increasing with respect to TMST

Doesn’t Daily_Production basically represent the number of rows of data you have per day?

I’m trying to understand why you used the PeriodOverPeriodDifference function instead of a simple distinct_count of CCNT.

If you set granulatury for TMST to “Day” and add distinct_count(GCNT) to your Value field well, doesn’t it give you the same result?

If distinct_count(GCNT) gives the right result for Daily Production, we can look at calculating the daily average next.

A distinct count of GCNT will not give the actual production rates.

No, the Daily_Production doesn’t represent the number of rows in my data. It is a calculated field to find the daily production rate. My embedded device is a printer and it continuously prints 24 hours a day. So I need to find out the production rate of each day, for that I have one unique field is GCNT,
it will increase with respect to the TMST.

For example,
if GCNT for 17-09-23 = 2000
GCNT for 16-09-23 = 1500
GCNT for 15-09-23 = 900
so production for 17 is 2000 - 1500 = 500
production for 16 is 1500 - 900 = 600

so average should be (500 + 600)/2 = 550

Can you put max(GCNT) and distinct_count(GCNT) in a column next to Daily_Production so that I can see the difference between those numbers?

TSMT Daily_Production max(GCNT) distinct_count(GCNT)
Sep 1, 2023
Sep 2, 2023
Sep 3, 2023
Sep 4, 2023
Sep 5, 2023
Sep 6, 2023

Hi @David_Wong, Kindly see the attached screenshot for your reference

Hi @Harsheena,

Are the values of GCNT sequential or are there duplicates values for GCNT? I’m trying to understand why there are only 222 unique values of GCNT on Sep 18 when the difference in MaxGCNT on Sep 17 and Sep 18 is that big.

I don’t think there’s a way to nest periodOverPeriodDifference with avg or avgOver, so I’m trying to see if there’s a different way you can calculate Daily_Production. Is there any way you can calculate Daily_Production in your dataset using SQL?

Hi @David_Wong,

Yes, GCNT values are usually sequential, and in most cases, they do not have duplicate values. However, there are instances when multiple events occur simultaneously within seconds, and in such cases, GCNT values can be duplicated. For example, if a device reboot, print activation, and cartridge change all happen simultaneously, it will generate these events with the same GCNT value. Once printing begins, the GCNT value will sequentially increase.

I am not sure about calculating the Daily_Production in my dataset using SQL.

Can we obtain this by using this way?

If it makes sense to use count(GCNT) to calculate daily production, you can calculate the average this way and then put it in a KPI visual:

``````avg(count(GCNT, [truncDate('DD', TMST)]))
``````

If you still want to calculate daily production based on the max of GCNT, try this instead:

``````(maxOver(GCNT, [], PRE_AGG) - minOver(GCNT, [], PRE_AGG)) / distinctCountOver(truncDate('DD', TMST), [], PRE_AGG)
``````

I tried the second formula based on the Max of GCNT, and this is the value I am getting for the average,

If I export this table into Excel and check for the average, the value again seems to be inaccurate

The expected average value is 250030 based on Daily production value.

Would a formula like this be helpful? I’m encountering an “Execution order mismatch” error, though.

(sumOver({Daily_Production}, , PRE_AGG)) / distinctCountOver(truncDate(‘DD’, TMST), , PRE_AGG)

Can you verify that the total production is correct?
maxOver(GCNT, , PRE_AGG) - minOver(GCNT, , PRE_AGG)

If you take the max GCNT in the dataset and subtract the min GCNT, that should give you the total production, right?

If the total production is correct but the average is not what you expect, then there’s an issue with the denominator (number of days). Does the dataset contain days with no data? If there are days with no data, when calculating the average, do you want to divide by all days or only by the days with data?

You can try `sumOver({Daily_Production}, [], PRE_AGG)` by itself and you’ll see that it doesn’t work. QuickSight doesn’t allow periodOverPeriodDifference inside the sumOver.