I am currently developing a report to visualize 24 hours of production data. My objective is to depict the production count for each hour along the x-axis, correlated with the respective timestamps. I am employing calculated formulas to ascertain the hourly production rate.
Initially, I determine the maximum value for each hour using the following procedure: maxgcnt = periodToDatemaxOverTime(max(GCNT), TMST, HOUR)
Subsequently, I calculate the variance as follows: mingcnt = periodOverPeriodDifference(maxgcnt, TMST, HOUR, 1)
Here, “GCNT” represents the counter value and “TMST” indicates the timestamp. By applying the ensuing formula: diff = maxgcnt - mingcnt
The resultant values from this computation are indeed accurate. Consequently, I have integrated this methodology into the visualization process. However, a recurring issue I encounter is the omission of the initial record, which corresponds to 12 am.
I am inquiring as to how I might resolve this dilemma and effectively incorporate the initial value into the report. While I attempted to use the “mingcnt” formula in the context of periodToDateminOverTime(min(GCNT), TMST, HOUR) to encompass the first value, regrettably, this approach introduces inaccuracies into the other production rates.
I would greatly appreciate any assistance in overcoming this challenge. I find myself at an impasse and any guidance provided would be immensely valuable.
Hello @Harsheena , welcome to the Quicksight community!
I’m still testing some things out to see why this might be happening, but for the time being could you try pushing the time back in your StartDate Control?
Based on the date format, maybe try August 7th at 11pm to see if that will at least show the data for August 8 12am.
Hello, @WLS-D . Thank you very much for your response.
I am currently trying to locate the production report for a specific day in a 24-hour format. Therefore, I am unable to adjust my start date to the previous day at 11 pm, as my customer specifically requires a 24-hour report for each day.
Regarding the first hour, how can I calculate the value or retrieve the production count for that initial hour? Is it feasible to compute the first value using an alternative calculated formula that subtracts from the last record of the previous day at 11 pm? Furthermore, can this value be incorporated into the visualization to fill in the missing initial records using flags or filter conditions?
I am currently facing a challenge at this juncture and any assistance would be greatly appreciated. Thank you.
My theory here is that because there is a control filtering the data on top of the calculated field that has an offset, it is stopping the 12am row from comparing against the row for 11pm the previous day.
Could you try something like:
ifelse(Ifelse({TMST} = Aug, 1 12am, periodToDateminOverTime(min(GCNT), TMST, HOUR, [PRE-FILTER}, {diff})"
Hello @WLS-D ,
Thank you for your response. Indeed, your observation is accurate. I am applying a date control filter for the start date, and it is stopping the 12 am row from comparing against the row of 11 pm on the previous day.
As per your logic, the formula you provided should work, but I’m encountering difficulties fixing it as it is resulting in errors. I’ve attempted various formula variations, such as:
Where StartDate is my parameter controlling the start time, and MaxGCNT is calculated using periodToDateMaxOverTime(max(GCNT),{TMST},HOUR) and holds a valid value.
I’m seeking guidance regarding the issues in my formula. Any assistance would be greatly appreciated. Thank you in advance.
Note: I have adjusted the start date to include records from the previous day at 11 pm, resulting in the visualization showing data from 12 am. However, my client’s requirement is to have a 24-hour visualization, such as from 12 am to 12 am or 7 am to 7 am.
Hello,
Can someone help me with this issue, As a manufacturing company, I have the requirement to generate hourly production reports within a 24-hour timeframe for multiple clients. I find myself stuck on this matter and would greatly appreciate any help that can be provided.
@WLS-D
Thanks for your reply.
Since the record is empty for 12 am I can’t right-click on it and If I right-click on the 2 am record, there is no exclude option.
@WLS-D
Hi, Somehow I managed to get this working by adjusting the start date to the previous day at 11 pm and then creating a flag for the missing record based on that flag created a filter to exclude the first missing record. Now my visualization is correct and accurate in that it shows from 12 am to 11 pm records.
Nonetheless, this still seems to be causing confusion for my clients. Is there a way I can modify the time format on the X-axis to display as “7 - 8 am” for 8 am, and “8 - 9 am” for 9 am format?
Hello @Harsheena , that’s awesome that you got that working, and thank you for posting the solution so we have it in the community!
My initial thought here, but it might add some work, would be to use a calculated field to change the names. Something like this:
ifelse( TMST = “12am”, “12 -1 am”, “12am”,
AND TMST = “1am”, 1 - 2am", “1am”, etc…)
