Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Cross Table Calculations

Feastie
7 - Meteor

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 DateEnd Date

123456     

 010124     

 070924

123457       050224       300924

 

The second, Table 2, has three fields and 73 rows.

 

YearMonthMonth End Date
20241310124
20242280224

 

I want to see where an item was live for each period, so the result would look like this:

 

Item No.Start DateEnd Date01-202402-2024
123245601012407092411
12345705022430092401

 

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.

5 REPLIES 5
OTrieger
13 - Pulsar

@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
19 - Altair

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
7 - Meteor

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
7 - Meteor

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
7 - Meteor

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.

Labels
Top Solution Authors