Dates column

[Tim6]

Hi There, 

I hope all is well.

I need to make a workflow to calculate a daily balance from Jan 1 - Dec 31.

However, the data I have only has the balance from December 31 2019 

Goal: Calculate the daily balance

Question: For every unique identifier, is there a way I can build a date function in Alteryx that will create dates for each month (Jan 1st – Jan 31st etc)?

Or would I have to use the formula tool to create individual dates in Alteryx for each month.

Please let me know what you think.

Thank you, 

[Jonathan-Sherman]

Hi @Tim6,

It sounds as though a generate rows tool would be your friend here, but could you just give an example of the input and output you'd be looking to achieve?

Regards,

Jonathan

[bpatel]

hi @Tim6 ,

i agree with @Jonathan-Sherman  sounds like generate rows would help. if you click on the generate rows example in designer you should find what you are looking for. hope this helps!

[Tim6]

thank you -i'm not sure i understand how to use this tool. 

Can you show me an example of building a date from Jan 1st- feb 29th? 

[markcurry]

Hi @Tim6 here's an example, if you have start and end dates files, this example creates a new Date field, starting off with the date in [Start Date] and adds a new day to that field  - DateTimeAdd([Date],1,"day")  until that date is <= to the [End Date] field.

[Tim6]

Thank you so much this is very helpful! 

@markcurry Is there a way I can set a condition using the expression editor to ensure that dates are not generated past a certain date say March 31st? 

[Jonathan-Sherman · Accepted solution]

Hi @Tim6,

Using the generate rows tool you could create dates between a "Date" and "EndDate" whilst also ensuring the Generated Date is below a threshold.

In this example i've got two records, the first will create dates between 2020-01-01 and (including) 2020-01-31, whilst the second will create dates between 2020-03-15 and (including) 2020-03-31 as it's hit the threshold of 31st March.

Input:

Example of output rows:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Tim6]

@ Jonathan - yes this solution worked thank you very much!. My last question is - is there a formula i can use to to insert the start date as January 1, 2020 for all of my records? instead of adding a start date of January 1st to all records in the underlying data? 

[Jonathan-Sherman · Accepted solution]

Hi @Tim6,

Yes you could simply enter '2020-01-01' into a formula tool (include the apostrophes to define as non-numeric) and store the field as a Date data type.

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan

[Tim6]

@Jonathan-Sherman  thank you! 

I actually have another question. Now that i am able to generate dates for each loan from Jan 1 - March 31st. 

I have another file that shows me the payment.

The payment would only be on a specific date (once a month). 

Using the formula tool, I would like the alteryx output to show the daily balance on each day and then on the date of the payment i would like to use the formula to show balance - payment. But it is not doing that. 

Can you please advise