Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Dates column

Tim6
8 - Asteroid

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, 

12 REPLIES 12
Jonathan-Sherman
15 - Aurora
15 - Aurora

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

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!

bpatel_0-1593544723566.png

 

Tim6
8 - Asteroid

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
12 - Quasar

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
8 - Asteroid

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
15 - Aurora
15 - Aurora

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:

Jonathan-Sherman_1-1593549496572.png

 

Example of output rows:

Jonathan-Sherman_0-1593549446245.png

 

 

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
8 - Asteroid

@ 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
15 - Aurora
15 - Aurora

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.

 

Jonathan-Sherman_0-1593550339963.png

 

 

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

 

Regards,

Jonathan

Tim6
8 - Asteroid

@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  

 

Labels