Hi There, This line chart displays the daily production count for each day based on the start day and end day filter applied.
Hello @Harsheena !
I think it could be a few things happening at once. First, how are you trying to drill down to hour? I see that you have a control labeled “Period”, is that what you are attempting to use to drill down?
Also, those two calculated fields are set to “Day” as their time period so that will be the data that they display. If you Period control filter has “Hour” as an option, or any other option you could create an ifelse statement like this:
Ifelse(
${Period} = 'Hour', trunc({Daily_Production}, 'HH'),
${Period} = 'Day', {Daily_Production},
${Period} = 'Month", trunc({Daily_production}, 'MM',
Null)
I used Daily_Production as an example but that sort of ifelse statement should attach whatever calculated field you choose that has a timestamp/date data type to be manipulated by your Period control filter. It is also possible that you may need to truncate the dates of your date field prior to using the ifelse statement.
@duncan Hi, Thanks for your reply.
Period Control is used for the conditional layout visualization. It has two values hourly and daily.
When hourly is chosen it will show yesterday’s 24-hour production data.
When Daily is selected it will show the start day and end day parameters, and based on that selection, it will show the daily report for that specific period.
And I clicked on the Drill down to Hour option from the visual.
Daily_Production = periodOverPeriodDifference({Daily_MaxGCNT},TMST, DAY, 1)
And for hourly rate = periodOverPeriodDifference(maxgcnt,TMST, HOUR, 1)
And maxgcnt = periodToDatemaxOverTime(max(GCNT), TMST, HOUR)
Daily_MaxGCNT = periodToDatemaxOverTime(max(GCNT), TMST, DAY)
These are my calculated fields to find the production rate.
Now could you please advise, thanks in advance!
Hello @Harsheena, the suggestion Duncan made I believe will be the best scenario for this. You can use the ifelse statement from the Period control to determine which value will show. One calculated field that will choose between Daily_Production or Hourly_Production then another for Hourly_maxgcnt or Daily_Maxgcnt. Then those are the fields you will put into the field wells. The drill down will no longer display on the visual and it will be entirely determined by the control value.
Another alternative would be to create 2 of the same visuals, one that includes the calculated fields for daily and another that includes the calculated fields for hourly. Since you are using Free-Form the conditional rules to hide or show a visual based on a parameter selection can be used. You can set both visuals to take up the exact same space and be shown when their respective control value is selected. I’ll attach an image to show you where you can see that option below:
Hi @DylanM,
Thank you for your response.
I have already implemented your second suggestion, which involves displaying hourly and daily production using the period parameter control with free-form layout conditional rules.
This is my hourly production report, which accurately shows the 24-hour production rate from 7 AM yesterday to 7 AM the following day, based on the user’s shift.
Now, the user wants to view the daily production report for a specific range of days by choosing the start and end dates. This is also functioning accurately.
However, if a user wishes to see the hourly production rate for a specific day, e.g., 03/09/23, the only way to achieve this is by using the “Drill down to hour” option to display a 24-hour chart for that particular day.
We have taken this approach to ensure that the hourly production chart aligns with the user’s shift, and I created a calculated field as per your suggestion here.
Here’s the calculated field we used, following your recommendation:
condition3 = ifelse(
addDateTime(7, ‘HH’, ${StartDate}) <= TMST AND TMST <= addDateTime(8, ‘HH’, ${EndDate}),
TMST, NULL
)
However, when I apply the start and end time parameters to filter using the condition3 field, it doesn’t provide accurate visual results when I change the start and end times to a specific day, such as 03/09/23. It only gives accurate values for the previous day.
My goal is to allow users to view the hourly production report for any day they select, either through the start and end time parameters or by using the “Drill down to hour” option in the daily visual.
Could you please advise on the best approach to present these visuals? Thank you.
Hello @Harsheena, what if you used the calculated field I suggested, eliminating the drill down feature, and rather than using drill down to go into a specific day you add another control that would allow the user to select a day and filter you visual based on that parameter value? I think you are getting into a territory with this visual that you are trying to implement too much functionality, and it might be worth splitting it into 2 different visuals to relieve the problems you are facing.
Hi @DylanM,
If I create two controls for the start time and end time and then use the calculated field in the filter for these parameters, again the visual will show for 8 a.m. to the following day 11 p.m., it isn’t limited to the next day 7 AM
Condition 3 = ifelse(
addDateTime(7, ‘HH’, ${StartDate}) <= TMST AND TMST <= addDateTime(8, ‘HH’, ${EndDate}),TMST, NULL
)
Yes, you are correct that showing two different visuals is better than the drill-down option, here if we could show the visual from 7 a.m. to the following day at 7 a.m. using the calculated field, then the user can always change the date using the controls, it will solve the problem however my calculated field doesn’t limit to 8 a.m
Hi @Harsheena, I forgot the hours were being handled by the calculated field rather than a filter. You wouldn’t need a filter then, they could just change the date connected to those parameters and it should work.
Hello @Harsheena, I mean you can create a control connected to the start date and end date parameters that will control the values for the calculated field. It will determine the day and then run the functionality through the addDateTime calculation that you created but you wouldn’t need an additional filter on the visual.
Hi @DylanM,
Could you please advise on the mistake I might be making here? The intention is to limit the visual to records from the following day at 8 AM, as specified by the calculated field. I suspect that either my calculated field isn’t functioning correctly or I’m overlooking something quite obvious.
In this context, “condition3” represents my calculated field, and I’ve applied the filter as shown in the image below:
Additionally, the filter for the original time field is applied as shown in the image below:
With both filters applied, the visuals appear to be the same. Therefore, I suspect that “condition3” is not working as intended.
Here are the parameters, and I created the control from them:
Furthermore, if I attempt to apply “condition3” to my X-axis instead of the original time field, it results in an error.
I apologize for asking numerous questions about this, but I’m struggling to achieve my desired results, and the release date for this dashboard is rapidly approaching.
My desired output is to display the visual data from 7 AM to the following day at 7 AM if users select any date from the controls.
Hello @Harsheena, this is going to be a lot to explain but I think I have resolved your date issue. I’ll do my best to talk through this.
So, I think the best solution with be a few steps. You want a user to select a day that will show them the hour by hour from 7am to 7am (a full 24 hours). My thought is, use a single parameter to display a control for the user to select a date (without giving them the ability to adjust the time), you will then use that single parameter (which will have time included in the default) to set your start and end times that you will use to display. I will outline each step here:
Create a parameter that will default to Start of Previous Day (this is assumed since you used this in your field above, you could use start of this day as well) and you will set it to the minute granularity.
Create a single control to select a date but remove the HH:mm:ss from the control value so the user only selects a day. The parameter will ensure the day value is returned to the minute 00:00 for the day selected every time.
Now, you will need a calculated field to create the end time from the single date parameter. It is going to look crazy but this is it:
addDateTime(1439, 'MI', parseDate(toString(${DATE}), 'yyyy/MM/dd HH:mm:ss'))
Now you are guaranteed to have your start time value to the minute granularity of 2023/09/20 00:00 and your end time to 2023/09/20 21:59 because that function will add enough minutes to set it to the last minute of the day.
From what I remember, you already have the ifelse to check if the start time + 7 hours is less than or equal to your date or your end time is greater than or equal to your date. Now you will use the single date parameter for your start time check and the end date calculated field for the other check.
A final option would be to create 2 calculated fields and avoid using addDateTime in your ifelse at all. It would always set the start and the start time to 2023/09/20 07:00 and end time to 2023/09/21 07:00 or whatever your want the final minute to be on the check by using the calculation for a start time calc field and end time calc field like this:
addDateTime(420, 'MI', parseDate(toString(${DATE}), 'yyyy/MM/dd HH:mm:ss'))addDateTime(1860, 'MI', parseDate(toString(${DATE}), 'yyyy/MM/dd HH:mm:ss'))And your ifelse would just check if your date is greater than or equal to Start AND less than or equal to End
@DylanM 
Thank you for your incredible support and help! Your assistance has been invaluable, and I truly appreciate it.
I have implemented all the formulas as per your suggestions, and yes, it calculates the start and end time to be 7 a.m., which is really helpful.
I attempted to incorporate these into my visualization, but it seems I still need to create a filter for the date parameter based on the user-selected date to display accurately. It worked for yesterday as my end date parameter, which was previously created, is set to relative dates at the end of the previous day with minute granularity.
However, if I apply any other date, the visual is inaccurate due to my filter’s EndTime parameter. How can we incorporate the calculated End_Time field into my visual to filter based on that?
Hello @Harsheena, I know this is similar to the question we were working on, but this topic is becoming rather verbose. Could you please initiate a new question about this so we can keep solutions related to the specific question being asked?
