Can I use a ETL or Beast mode to calculate starts and ends and show in a chart?

kim_barragan0126

I have data showing the start and end date, such as the below. I want to use a chart to show starts & ends side by side by week. Is this doable with a ETL or beast mode?

CandidateName	CompanyName	StartDate	ActualEndDate
Name1	Company1	12/23/2024
Name2	Company2	12/23/2024
Name3	Company3	12/23/2024
Name4	Company4	12/23/2024
Name16	Company16	12/26/2024
Name17	Company17	12/26/2024
Name18	Company18	12/26/2024
Name19	Company19	12/26/2024
Name20	Company20	12/26/2024
Name21	Company21	12/26/2024
Name22	Company22	5/25/2024	12/22/2024
Name23	Company23	5/25/2024	12/22/2024
Name24	Company24	5/25/2024	12/22/2024
Name25	Company25	5/25/2024	12/22/2024
Name26	Company26	5/25/2024	12/22/2024
Name27	Company27	5/25/2024	12/22/2024
Name28	Company28	5/25/2024	12/22/2024
Name29	Company29	5/25/2024	12/22/2024
Name30	Company30	5/25/2024	12/22/2024
Name31	Company31	5/25/2024	12/22/2024

The chart should look something like the below.

ColemenWilson

Yes you can accomplish this using Magic ETL.

Step 1: Split out your input data into two groups: Starts and Ends. Use two formula tiles.

Formula tile #1 (Starts) will look like this:

Formula tile #2 (Ends) will look like this:

Step 2: Append these groups together using the Append Rows tile:

Step 3: Use a filter tile to remove rows where the newly created `Date` field is null

Step 4: Create a card that is the Grouped Bar chart type. Use the newly created Date field as the Category, a count of the candidate name (or combination (concatenated) name that is both candidate + company, or some other unique identifier as the Value. And the newly created Group field as the series and voila!

Let me know if you get stuck or have any questions!

ColemenWilson

Yes you can accomplish this using Magic ETL.

Step 1: Split out your input data into two groups: Starts and Ends. Use two formula tiles.

Formula tile #1 (Starts) will look like this:

Formula tile #2 (Ends) will look like this:

Step 2: Append these groups together using the Append Rows tile:

Step 3: Use a filter tile to remove rows where the newly created `Date` field is null

Step 4: Create a card that is the Grouped Bar chart type. Use the newly created Date field as the Category, a count of the candidate name (or combination (concatenated) name that is both candidate + company, or some other unique identifier as the Value. And the newly created Group field as the series and voila!

Let me know if you get stuck or have any questions!

kim_barragan0126

Thank you! I was going down the wrong path trying to use Group By or Dynamic Pivot. Thank you!