Daily Revenue Split and Monthly Grouping

[CalvinA]

Hi everyone!

I have the following fields:

Start date, e.g. 2017-06-12
End date, e.g. 2017-09-04
Revenue, e.g. 76,182

In the above example, the difference between the Start date and End date is 84 days. I want to divide the Revenue over the days equally and then be able to classify them into months.

So using the above example the days would be allocated as:

Jun: 18 days
Jul: 31 days
Aug: 31 days
Sep: 4 days

And the Revenue split among the days in the month in the correct ratio:

Jun: 16324.71
Jul: 28114.79
Aug: 28114.79
Sep: 3627.71

I'd also like to set the Start date as today's date every time I run the process. Finally the ability to restrict to 2 months in advance from the current month would be awesome, so the above would give results for Jun, Jul and Aug.

Many thanks in advance!

[gc]

I'd start by looking at the DateTimeLastofMonth() and DateTimeFirstofMonth() functions for use in a Formula tool, so you can get your time intervals calculated in each month. Once you have that, you can start computing ratios to be applied to your Revenue amounts.

[CalvinA]

Thanks for the reply. I tried to put together a solution (let me caveat this by saying that I just started using Alteryx!) but I could only split the Revenue equally across the months (which is not what I want). I also faced several run errors using the DateTimeToday() function before turning here for help.

I've looked into the functions you mentioned but I'm a bit confused about their usage here?

[gc]

So see attached. It was my bad on DateTimeLastofMonth() references...you need to use DateTimeTrim(). I think this will get you to where you need to be.

Edit: The updated workflow with the "days" calc.

[NicoleJohnson · Accepted solution]

How about something like the attached? Requires a few date formulas to get where you're going, but I think this might achieve what you're trying to do...

1. Calculate Start Date based on today's date, the number of days between start & end date (adding 1 day to include the start date in the number of days, which actually makes your above example 85 not 84), and then calculate the Daily Revenue amount.

2. Generate a list of all days between start & end date, then calculate the first & last day of each month contained within that list, then summarize by first/last day of month

3. Append the original info to that summarized list of months

4. Calculate Days in each month based on month start & end date (this gives you the # of days in each month), then calculate monthly revenue

5. Filter for only those dates that are in the next two months (I was a little clear about the objective of this one, so made my best guess at what you wanted to see... let me know if that wasn't quite right!)

Hope this helps, should at least give you a few new tricks to try and some clarity on the date formulas. If you want to know more about DateTime formats & formulas in Alteryx, check out some of these articles:

Alteryx Help: DateTime Functions

Alteryx Knowledge Base: When TIme is of the Essence

... or just search for "DateTime" in the community, there are a ton of examples and links to learn from!

Good luck, let us know if we can assist you further! :)

Cheers,

NJ

[CalvinA]

Hi Nicole,

Thank you so much for your reply. It's a great solution, many thanks for this! I just have one issue, I have many lines of dates, when I add another date in the workflow as a test, I can see multiple months and revenue lines?

[Amy_C]

Do you need future dates also? I don't see those appearing. Unless I'm mistaken. 

Edit: I was wrong. Please ignore.

[NicoleJohnson]

@CalvinAif you have multiple dates you're doing this for, you probably want to add a RecordID at the beginning, then include a Group By record ID number in the Summarize tool, then use the Join (by RecordID) rather than the append tool to get the months added back to your data. Does that help?

NJ

[CalvinA]

Hi Nicole,

Sorry to be annoying, I've never used the record tool in a process before. Would you be able to show me a quick example of what you mean? I always learn by reverse engineering solutions.

Thank you greatly!

[NicoleJohnson]

Not a problem at all! I learn the same way :) See attached workflow for an example! And let me know if you need any more assistance!

NJ