Cross Table Calculations
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
I have two tables that I need to combine using a calculation.
The first, Table 1, has three fields and thousands of rows.
Item No. | Start Date | End Date |
123456 | 010124 | 070924 |
123457 | 050224 | 300924 |
The second, Table 2, has three fields and 73 rows.
Year | Month | Month End Date |
2024 | 1 | 310124 |
2024 | 2 | 280224 |
I want to see where an item was live for each period, so the result would look like this:
Item No. | Start Date | End Date | 01-2024 | 02-2024 |
1232456 | 010124 | 070924 | 1 | 1 |
123457 | 050224 | 300924 | 0 | 1 |
I can perform this quite manually by using a cross tab to switch the orientation of the dates and joining the two tables on record number.
I would then copy the dates to all rows using a Multi-Row Formula.
For each month I would perform a calculation to determine if the item was live in each period.
I can do this but, given that there are 5 years, each with 12 months, this would take a long time to set up.
Is there a quicker way to perform the calculation by looking up the relevant month end date based on the field heading (i.e. look for the start date of item 123456, check the Start Date is before the Month End Date from the second table and the end date is after the Month End Date from the second table)
Any pointers would be massively helpful.
Thanks, Feastie.
Solved! Go to Solution.
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Feastie
One way to do it, not even sure if the best way, but one way to do it is as following, get the dates to be in a standard Alteryx format and then get the year and month for each of the rows. Do a join the data based on Month and Year for Start Date and join again for End Date and then you will have the values from Table 2 in your Table 1 and then you an use formulas to check it for Start date and End date
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
you will use a generate rows on your first table to create rows for each active month. you can then crosstab. I don't even think you need your second dataset unless you have non-standard end months.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Yes, we have non-standard months. It is a fiscal calendar, running March to February and the month end dates are not calendar month end.
That is why the only way I can see currently is to perform a manual calculation for each.
I would also like this to be dynamic based on today's date, but I have a solution for that, providing I can sort an automated calculation.
I will try with the generate rows method and report back. Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
So, I understand the method of generating new rows @apathetichell , however, as my months are not calendar months, is there a way to combine the two tables in order to create a column that uses the start end end dates in relation to my fiscal months?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks @apathetichell,
I have used your suggestion using calendar dates and, you are correct, I don't necessarily need the second data set however, I will reference this back in to give me counts to our fiscal periods. This will potentially not count certain items in the correct fiscal period particularly if the launch date is close to the beginning, or end of the month (where fiscal dates don't always coincide with calendar dates) but it will give me a steer towards the information I need.
Many thanks, Feastie.
