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 date15306090180 06/19 - 2º Q636.735900.1618027.0716107.8913982.50 07/19 - 1º Q806016.6632675.8652986.7341108.4511652.21 07/19 - 2º Q3755.2946.53503.0636.283920.52 08/19 - 1º Q30875.708903.767635.31630.61 08/19 - 2º Q12450.283132.562819.7825.23 09/19 - 1º Q83724.0723383.5519653.85 09/19 - 2º Q70917.9515023.946191.93 10/19 - 1º Q87190.816549.80 10/19 - 2º Q43045.42 11/19 - 1º Q4111.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 Example_Fortnight_separation.yxmd

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

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

Example_Fortnight_separation.yxmd

Datasets

Dynamic Processing

Iterative Macro

Accepted answers

BrandonB

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.

All comments

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

AME_sample.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703