Avg over a specific time frame based on the filter selection

Question & Answer
, , ,
1

I have a table with two fields: session ID, date_created

I have a filter to select start and end date_created and I need to calculate avg number of sessions per day in a specific time frame.

Here is my thinking:

I need a calculation that would count all sessions that happened in the selected time frame (count distinct, let’s say there were 60 sessions in the selected time period), then calculate how many days are between the selected start and end date (let’s say the selected time frame has 6 days). Then I want to take all sessions in the timeframe and divide by days of the time frame to get avg per day (60 sessions/ 6 days= 10 sessions per day).

I would appreciate any help creating a calculated field.

2

In QuickSight, you can create calculated fields using the calculated field editor. Here’s how you can create the calculation for average sessions per day:

  1. First, create a calculated field for the number of distinct sessions:
distinct_count({Session ID})
  1. Then, create a calculated field for the number of days in the selected time frame:
dateDiff({Date Created}, min({Date Created}), max({Date Created}), 'DAY') + 1
  1. Finally, create the calculated field for the average sessions per day:
distinct_count({Session ID}) / (dateDiff({Date Created}, min({Date Created}), max({Date Created}), 'DAY') + 1)

To use this in your analysis:

  1. Add your date filter to the analysis.
  2. Add this calculated field to your visual.

The calculation will update based on the date range selected in your filter.

3

Hi @Iwona,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post or were you able to find a work around?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

4

Hi @Iwona,
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 post in the community and link this discussion for relevant information if needed.

Thank you!