Aggregating objects by time range

Question & Answer
,
1

hi,

I have a dataset in the below structure:
| Id | startDate | endDate |

|111|02/04/2022|06/04/2022|
|222|01/04/2022|04/04/2022|
|333|04/04/2022|06/04/2022|
|444|03/04/2022|06/04/2022|

I want to know how many id’s are exist within each date in the time range.
so the result should appear as below-

| Date | idCount |

|01/04/2022|1|
|02/04/2022|2|
|03/04/2022|3|
|04/04/2022|4|
|05/04/2022|3|
|06/04/2022|3|

is there any aggregation function that enables do preform this kind of calculation?
do I have to make a data preparation of joining time dimension of all relevant dates with the original dataset where dim_time.date between startDate and endDate?

thanks,
Amihai

2

Hi,
The easiest way I can think about is using 2 filters, one for the StartDate and other for endDate. Create 2 dates picker like in the image to filter the range you want to analyze.
In a second visual, a KPI for example, you can load a calculated field:
distinct_count({Order ID})

distinctID
distinctID1336×834 68.9 KB

3

Thank you, but my purpose was to aggregate # of Id’s per day in the time range.
distinct_count doesn’t provide it.

4

Can you take the distinct count of id’s divided by the distinct count of days? That would get you an average of id’s per day

5

Hi, @amico. Did the response from @Max answer your question? 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 Q&A Forum!