Fill in columns if included between a certain timeframe

[GdeH]

Hello, 

In the example below, I have as input the 3 first column, I would like to create all other columns. 

I would like to write the amount in the column of the month if the month is included between the begin date and end date, if it is not included between those date I would like to have a 0 amount. 

Would someone have any idea how I could automate this in Alteryx? 

Amount	Begin Date	End Date	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
5000	01/01/2019	31/12/2019	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	0	0	0	0	0	0	0	0	0	0	0	0
1000	01/01/2019	31/12/2020	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000
2000	01/05/2019	31/04/2020	0	0	0	0	2000	2000	2000	2000	2000	2000	2000	2000	2000	2000	2000	2000	0	0	0	0	0	0	0	0
4000	01/04/2020	31/12/2020	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	4000	4000	4000	4000	4000	4000	4000	4000	4000

[AngelosPachis · Accepted solution]

Hi @GdeH ,

You can do that by generating the intermediate dates through a generate rows tool, then assigning an ID into each month to maintain the chronological order when dates will be placed on your column headers and then cross-tabing to the format you desire.

Hope that helps, let me know if that worked for you or you have any questions on the workflow.

Regards,

Angelos

[GdeH]

Hi @AngelosPachis 

Many thanks for your answer. 

I see now that my reply was not send out ... 

I would like to add a step to this with a formula, by splitting the amount all over the duration. 

For example Begin data 01-01-2019 and End date 31-12-2019, for an amount of 12.000. 

I would like to have 12.000/365*31 for JAN 2019 

12.000/365*28 for FEB 2019

12.000/365*31 for MAR 2019

12.000/365*30 for APR 2019 And so on. 

Would that be easy to implement? 

[AngelosPachis · Accepted solution]

Hi @GdeH ,

Yes that's doable with a formula tool. I've added one between the generate rows tool and the select tool. All you have to do is use two expressions, the one to calculate the days in a month (by subtracting the first of this months from the last date of that month) and the other for the expression you have provided (I also rounded to 2 decimals, you can remove the round function)

So your results look different now

Let me know if that worked for you.

Cheers,

Angelos

[GdeH]

Hi @AngelosPachis 

Thank you, it's working for a lot of items, however for some items I have a begin and end date in the middle of the month for example, 

24/05/2019 to 23/05/2020. 

For those items, the first formula should be "Amount*8/365" and for the last month it should be "Amount*23/365"

for all the months in between the formula will work. 

It could also be that the period is more than 12 month so for the end date is not always the 12th month.

For Example, 24/05/2019 to 23/07/2020. 

I'm struggling to implement this in the formula.. 

Could you help on this as well? 

Many thanks

Kind regards, 

Géraldine

[AngelosPachis · Accepted solution]

Thanks for providing more info Geraldine @GdeH 

Do you mind trying the attached workflow? I have added an extra row in the text input, testing what happens if you have dates other than the firs/last of each month.

I will explain the logic behind it once we make sure that it works as we want it to.

Regards,

Angelos

[GdeH]

 Hi @AngelosPachis 

Many thanks for your help on this. 
I changed the formula slightly and it is working. 

Kind regards, 

Géraldine