How to find Min value for the running Sum?

Question & Answer
, , ,
1

There is a Calculated field which consist of a runnable sum of a data which depends on day . Example if we select July 24 it shows the runnable sum of data in every hours . But now i need the minimum value of data of the day that means a single dot will be there if i plot it against a single day’s hours,

2

Hi Vimal,

have you tried this minOver({YourDataField}, [{YourDateField}], PRE_AGG)?

3

Yes, But it is how showing a error - “Execution order mismatch: PRE_FILTER calculations can’t have PRE_Agg operands.”

4

Hi can you please share some sample data? which data types you are using.

5

runningSum
(
sum({files sent}+{files received}),
[{updated_timestamp} ASC]
)

Hi,
This is a calculated field, where ‘files sent’, ‘files received’ and ‘updated_timestamp’ are another calculated field. In ‘updated_timestamp’ it is in hours, so from this calculated field, I get the number of files sent in different hours and can see the fewest file transfers from that. But now we need a period of dates, like if we set the start date as July 1 and the end date as July 4. I need to get a line graph connecting the least number of file transfers for each day.

6

Hi @Vimal, to achieve the minimum value of data for a single day in Amazon QuickSight, you will need to create a calculated field that identifies the minimum value for the day and then use this field in your analysis. Here are the steps to do this:

  1. Create the Dataset: Ensure your dataset includes the date, hour, and data fields.
  2. Create a Calculated Field for Minimum Value: You need to create a calculated field that calculates the minimum value of the data for each day. Here’s how you can do it:a. Go to the analysis where you want to create the calculated field.b. In the Fields list pane, choose + Add, and then choose Add calculated field.c. Name the calculated field, for example, MinValueOfDay.d. Enter the following formula:
minOver({data}, [{day}])
  • {data}: Replace with the actual field name for your data.
  • {day}: Replace with the field name that represents the date, ensuring it does not include the time component.
  1. Use the Calculated Field in Visual: Now you need to create a visual that uses this calculated field.a. Choose the Visualize pane.b. Add a new visual, for example, a scatter plot.c. Drag the day field to the X-Axis.d. Drag the MinValueOfDay calculated field to the Y-Axis.e. Ensure that you have a single point for each day, representing the minimum value of data for that day.
  2. Optional - Filtering for Specific Day: If you want to filter to show the data for a specific day, you can add a filter:a. Choose the filter pane.b. Add a new filter on the day field.c. Set the filter to the specific date you are interested in (e.g., July 24).

These steps will give you a visual that plots the minimum value of data for each day, showing only a single dot for each day’s hours if filtered accordingly.

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

1 Like
7

I tried this but the result is wrong. Is it possible to create a timeline that includes data from two different timescales? I want to show the date on the x axis, aggregated by day. On the y axis, I want to show the minimum value for that day, aggregated by hour. Is this possible?

8

Hi @Vimal,
It’s been awhile since last communication on this thread, are you still encountering the same issue or were you ablet o find a work around for your solution?

If you’re still having issues, please follow advice above and create an Arena view for us to more properly assist in this matter.

If we do not hear back within 3 business days, I’ll go ahead and close out this topic.

Thank you!

1 Like
9

Hi @Vimal,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.

Thank you!