Calculate Days in Financial Year, Annualized spend, Financial Year Spend

[SubbuSharma]

Hello, 

I am trying to to figure out to calculate "Financial year between two dates", "No. of Days in specific Financial Year", "Annualized spend", "Spend related to specific Financial Year" & "Contracts that are expiring in next 90 days" (this has to be dynamic as we do the reporting every week).

Please note my financial year is 01-Nov-2019 to 31-Oct-2020.

I have attached the sample contracts report that i would need your help on. much thanks for all your help in advance.

Thanks

Subbu

[DavidP]

Hopefully this is a good starting point.

You have to decide if you need to include records that don't match between source1 and source2.

I've used DateTimeNow() for determining FY, days in current FY and expiry in 90 days.

I've you can provide more info on how to calculate the other fields, I can help you out with those.

[SubbuSharma]

Thank you, David. This is really helpful.

I have attached the dummy data (35 contracts) for which I am trying to calculate the highlighted fields in column E - I.

meanwhile, I'll play around with the workflow that you have provided. Much thanks again.

[SubbuSharma]

just to add, "Days in F20" column will have to calculate the below formula.

=IF(E6>=Dates!$A$2,0,IF(F6>Dates!$A$1,MIN(Dates!$A$2,F6)-MAX(Dates!$A$1,E6),0))

in other words

=if (start date>=31-Oct-2020,0,if(end date>31-Oct-2019,Min(31-Oct-2020,end date)-Max(31-Oct-2019,Start date)

[SubbuSharma]

attached excel file (refer tab "contract analysis") will give us the formulas that have been currently used to derive the numbers in columns K - N

[DavidP]

Hi @SubbuSharma, thanks for the clarifications.

I have replicated the logic in Alteryx based on the last dummy file with Excel formulas you sent.

Note that the column FY on which many of the calculations are based is dynamic and determined from the date that the workflow is run. This means that when you run it in the 1st Nov 2020, FY will switch to 21 automatically.

Let me know if you have any questions.

[SubbuSharma · Accepted solution]

Hi @DavidP,

That was superbly done and Alteryx numbers matches to my excel workbook. This automation will atleast save me 5 hours of work on a bi-weekly basis.

Thank you so much for all your help and patience. Magnificent work!

thank you David,

have a great one!

[SubbuSharma]

Hi @DavidP - I am getting this error message while running the workflow for Annualized spend. Could you please let me know what step I am missing here.

[DavidP · Accepted solution]

The formula expects both Contract Term Days and Contract Value (CAD) to be numeric data types. It looks like one of them is a string.

You'll notice in my example workflow that I converted all amounts to fixed decimals and all whole numbers such as number of days to integers.

[SubbuSharma · Accepted solution]

great stuff, had one the calc as string rather than fixed decimals.

thank you again.