Daily Revenue Split and Monthly Grouping

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!

Date Time

Accepted answers

NicoleJohnson

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,

MonthlyRevenue.yxmd

All comments

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703