Cross Table Calculations

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.

Datasets

Accepted answers

apathetichell

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.

All comments

OTrieger

@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

apathetichell

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.

Feastie

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.

Feastie

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?

Feastie

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703