Compare a beast mode to an arbitrary date range

Options

I've been asked to create a trend that shows the number of open projects (y-axis) for an arbitrary date range(x-axis). A sample of my data looks like this:

Project_ID

Date_Opened

Date_Closed

1

1/1/2024

4/5/2024

2

1/18/2024

4/6/2024

3

2/2/2024

3/1/2024

4

2/9/2024

5

2/27/2024

6

3/14/2024

4/20/2024

7

4/3/2024

8

4/11/2024

9

4/30/2024

10

5/1/2024

The users want to be able to choose a date range, say 4/1/2024 to 4/7/2024, and see how many projects were open for each date interval in the range they selected (trending). The data above would yield a result like this:

4/1/2024

4/2/2024

4/3/2024

4/4/2024

4/5/2024

4/6/2024

4/7/2024

Open Projects

5

5

6

6

5

4

4

I'm struggling to create an x-axis of dates independent of dates in my data that I can use in a beast mode to compare to open & closed dates. Is there a way to do this without building it in the ETL? Currently I use a couple variables for custom date ranges, but I've never had to create a range where the dates in my data are not the relevant axis. I'm trying to avoid adding a count by date in my dataset because there are several other metrics I'll need to group by which will result in a large dataset only used for this card.

Thanks for any advice!

Answers

  • Sean_Tully
    Sean_Tully Contributor
    Options

    Hopefully someone knows an easy way to do this, but the only way I know how to do this would be through ETL by cross joining a set of dates against the project datasets. The flexible joins in ETL help a little bit - the join would be something like custom_date >= date_opened and custom_date < date_closed.

    Going to track this in case there's an easier way, would love to know it if there is!

  • AJOslund
    AJOslund Member
    Options

    That is similar to what I did for the first iteration of this card. The request was weekly intervals over a fixed period of time so I was able to keep the size of the dataset down with a count for each date/metric. This one will be a few orders of magnitude larger if I build it the same way, so hopefully I can avoid that!