3

I have a event dataset in QuickSight, where each record has two date fields: startDate and endDate. An event is considered active at time T if T is between startDate and endDate. I want to build a visualization of active events over time as a line chart. However, I am struggling to understand how that can be done in AWS QuickSight. Any ideas?

Tofig Hasanov
  • 3,303
  • 10
  • 51
  • 81
  • Ok, So Basically you need an aggregated numbers of active events on the time graph ? – Nagesh Salunke Jun 28 '18 at 10:05
  • @nagesh Yes. I want to see a graph of count of active events over time. – Tofig Hasanov Jun 28 '18 at 20:46
  • 1
    Can you please provide some dummy data for more understanding and plotting ? I am assuming you need to plot general date on X axis and aggregation based on active event definition ( An event is considered active at time T if T is between startDate and endDate ) – Nagesh Salunke Jun 29 '18 at 05:51
  • I ran into the same problem. To make it more clear, I have updated the post with a sample dataset. – hax Apr 15 '21 at 16:53

1 Answers1

-1

You can add a calculated field "Active" as follows:

ifelse(
  dateDiff(T,startDate)>=0 and dateDiff(endDate,T)>=0,
  1,
  0
)

Now, you can add a filter on that field and plot a line graph with T on x-axis and count or sum(Active) on y-axis and drill down as per your requirement.
Hope this helps.

Shreyansh Nawlakha
  • 1
  • 1