Hi There,
Thanks in advance!
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
Hi @Bhasi_Mehta , Thanks for your reply.
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?
@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
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.
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
Now could you kindly advise
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 @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.
