Link DateTime Field in the parameter to a dataset field

[Harsheena]

Hi Team,
I am developing a dashboard for my clients, where they can view their production data. I have implemented two parameters for the Start Date and End Date. By selecting these controls, users can generate hourly and daily production reports.

The challenge I’m facing is that when I apply the date filters of the Start Date and End Date, it tends to miss the first record due to the filtering. To address this issue, I’ve created a calculated field to find the previous day’s value using the formula (addDateTime(-1,'DD',${StartDate})).

Now, I want to apply this previous day’s value as the filter instead of the Start Date. My question is whether it’s possible to link this calculated field, created at the data source level, to the date field filter. While it’s straightforward to link parameters to string and other data types, I’m unsure about how to handle date-time fields.

Can you provide guidance on how to link my calculated field to the date field filter in the dashboard?

[DylanM]

Hello @Harsheena , why is the value being excluded when the filters are applied? Is the first value technically linked to the day before? I have an idea to test it out but the time granularity you would select would relate to the Datetime the missing field is related to. Try this out and let me know how it works:

Create a calculated field that will account for the first field of the day and the last, I was thinking something like this

ifelse(
addDateTime(-30, 'MI', ${STARTTIME}) <= {Date} AND {Date} <= ${ENDTIME} ,{Date}, NULL 
)

Then create a filter for that calculated field (instead of using the original date field in the visual) that will take a rolling start date and end date (Beginning of yesterday and end of today) and select the exclude nulls field), then this should show the single value you are missing. I’ll include an image for the filter below

[Harsheena]

Hi @DylanM,

Thanks for your support.

Yes, the first value is technically associated with the previous day’s records. Now, my hourly production report for 24 hours and daily production count is accurate.

If the customer wants to view the production count for a period of, for example, one week, they will select the start day and end day from the date parameter. In the line chart bars, it should display the daily production count, and this is what I’m trying to achieve.

I calculate the maximum daily value using the formula: periodToDatemaxOverTime(max(GCNT), TMST, DAY) (where GCNT is the counter and TMST is the time). This gives me the Daily Max value.

Then, I calculate the Daily Prod value by comparing it against the previous day using: periodOverPeriodDifference(DayMax, TMST, DAY, 1).

The solution you offered shows only yesterday’s count, but I want to display the production count for all days based on the user’s selected start day and end day.

Here’s what I’ve tried: I calculated the difference by subtracting the max value and min value for days and then included it in my visualization, but I filtered it to display only for the first record. However, I can’t consider this a solution as the value is inaccurate.

My idea is that maybe if we can retrieve the previous day of the start day the user applied, and then filter between the previous day (start day) and the end day, ignoring the nulls, I will get an accurate daily report.

Additionally, I have one more condition to consider: my customer’s shift is 24 hours, from 7 AM to the next day 7 AM, i.e., a full 24-hour period.

Could you kindly provide some guidance and advice? Thanks a lot in advance.

[Harsheena]

Hi There,
I just found the previous day and end day for the start day and end day controls, and created another calculated field like ifelse(
{previous_Day} <= TMST AND TMST <= endDay,TMST, NULL
) and applied this field to the filter instead of the Time field, but it didn’t help me.

Actually, I am missing both the first and last records in this visual. Which is 01-09-23 and 10-09-23 records from the graph

[DylanM]

Hello @Harsheena , what I am thinking is that instead of using the control that determines start and end date to filter your visual, you will use those parameters in a calculated field so you can use addDateTime to access the day before the start date and the day after the end date.

It will be an ifelse that will return the desired value if the date is in between those days.

Something like this:
ifelse(
addDateTime(-1, ‘DD’, ${STARTTIME}) <= {Date} AND {Date} <= addDateTime(1, ‘DD’, ${ENDTIME}) ,{DAYprod}, NULL
)

You will do the same thing for the tes value and then replace your bars fields with the 2 new calculated fields. Then you can add a filter for those fields and use the exclude nulls feature to make sure only values you want will be seen.

[Harsheena]

Hi @DylanM, Thanks for your reply.

When I create a new calculated field like the above I get the following error “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.”

[DylanM]

Hello @Harsheena , this may require you to use PRE_AGG in your calculated fields that include level aware calculations that are being returned in the ifelse statement. I believe this will resolve your issue.

[Harsheena]

Hello @DylanM,

When I apply “PRE_AGG” in my calculated fields, I encounter an error: “Execution order mismatch: PRE_FILTER calculations can’t have PRE_Agg operands.” These are my calculated fields, as shown in the images below:

Somehow, I managed to retrieve the first missing records from my visualization by using the “DateHider” calculated field and creating a filter for this field to pass start and end day parameters:

Now, my goal is to calculate the average daily print count based on these daily production values. My idea was to obtain the sum of daily production divided by the distinct count of days, as I tried using the “avgOver” and “avg” functions with “PRE_AGG.” However, I’m encountering the same error with level-aware calculations.

Here, I’m also getting inaccurate values for the daily average print due to the first missing record in the “Daily Production” value. I attempted to apply the same “DateHider” filter here as well, but it resulted in inaccurate running sums.

I’m sure I am missing something very obvious. Could you kindly provide some guidance? Thank you in advance.

[DylanM]

Hello @Harsheena, I want to follow-up on this portion of your question:

Now, my goal is to calculate the average daily print count based on these daily production values. My idea was to obtain the sum of daily production divided by the distinct count of days, as I tried using the “avgOver” and “avg” functions with “PRE_AGG.” However, I’m encountering the same error with level-aware calculations.

But I think this is a bit out of scope from your original issue related to displaying data for certain dates. If you could post a new topic for this particular issue, we can help resolve that calculated field problem because it seems the date display issue has been resolved. Am I correct on that or is there still an issue getting data from the start and end times to display?

[Harsheena]

Hi @DylanM,

I have resolved the issue of retrieving data between the start and end times in my line chart visualization. However, I’m now facing same challenge in finding the average daily production. The problem arises because the first record is missing when the start date and end date filters are applied, which is similar to my previous issue.

I’m considering either creating a new question regarding this problem or seeking advice here, as I’ve already posted this question there.

I want to express my gratitude for all your assistance and suggestions. Thank you very much!

[DylanM]

@Harsheena, I believe that other ticket would be a great place to follow up on your problem getting the average. I’ll set this topic as solved and I can follow up with you on the other question. Thank you!