Can I use a loop to segregate data into time periods?

[cframos]

I'm trying to create a workflow to summarize values from several months of data in a fortnight basis, i.e. for every month I need to extract a sum of values from the first and the second half of the month. The final result is a Matrix with the same number of columns as of rows. I was able to do that in Formula for 5 periods (15, 30, 60, 90 and 180 days), but to get the full result I would need to right a formula with 70+ ELSEIFs, so I was looking for a solution to do it iteratively, where I'll have the date in rows and the 15-day periods in columns, Something like that:

Due date	15	30	60	90	180
06/19 - 2º Q	636.73	5900.16	18027.07	16107.89	13982.50
07/19 - 1º Q	806016.66	32675.86	52986.73	41108.45	11652.21
07/19 - 2º Q	3755.29	46.53	503.06	36.28	3920.52
08/19 - 1º Q	30875.70	8903.76	7635.31	630.61
08/19 - 2º Q	12450.28	3132.56	2819.78	25.23
09/19 - 1º Q	83724.07	23383.55	19653.85
09/19 - 2º Q	70917.95	15023.94	6191.93
10/19 - 1º Q	87190.81	6549.80
10/19 - 2º Q	43045.42
11/19 - 1º Q	4111.53

But, it would be a table with one row for every 15 days (the full dataset will have 5 years of data)

Is there a way to do it easily?

Thanks

[BrandonB]

Would it be possible to use a Transpose tool to get all of the values from each column into a single column to then use one formula to dynamically run across all of them? It could then be crosstabbed back afterwards. 

[danilang]

hi @cframos 

Can you attach AME_Sample.xlsx. 

Dan

[cframos]

Hi @danilang

There it is

[BrandonB · Accepted solution]

Does each period range follow this pattern?

0-15 days is 15 days

15-30 days is 15 days

30-60 days is 30 days

60-90 days is 30 days

90-180 days is 90 days

Would the next periods be 

180-270 days being 90 days

270-450 days being 180 days

450-630 days being 180 days

My initial thought to approach this would be to take [Pay_down_difference_days]/15, drop off the decimal from the result and then multiply by 15 in order to get the closest multiple of 15. The other piece that needs to be determined is how the ranges increase with the higher values. 

[cframos]

Actually, the Pattern is more like:

< 0 (payment before due date) to 15 (payment until 15 days after due date = 1 (reference to first period of 15 days)

16 days to 30 days  = 2 period of 15 days due

31 to 45 = 3

46 to 60 = 4

61 to 75 = 5

...

166 days to 180 days = 12 periods od 15 days

180 to 195 = 13

...

and so on.

Since I may have as long as 120 periods (1800 days), it would not be viable to right one line for each period.

I'll try to do something like you said, by dividing and dropping the decimals. But it would be great if Alteryx had a way to apply a for loop to do that, as I would do in Python.