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

Need help with Hourly Benefits formula

FrankB724
6 - Meteoroid

Hello Community!

I'm building a workflow that helps to calculate and report our daily labor costs for various locations. One of the pieces I need to incorporate into my workflow is an "Hourly Benefit Days" field, which calculates a monthly rate of $200,000 divided by the number of days in a month (31 because of October) and then multiplied by the current number of days (15 as of today).

In excel this formula pulls in the proper date numbers from other cells and easily calculates as =200000/31*15. (*Note: the numbers 31 and 15 in this example come from cells such as A2 and B3 so the formula truly is =200000/A2*B3).

I'd like an Alteryx formula that, when I open the workflow daily, automatically takes the current day/month numbers into consideration. I have the following "DateTime" formula strings working separately:

 

DateTimeFormat(DateTimeLastOfMonth(),"%d") This piece gets me a number of 31 (for October)

 

DateTimeFormat(DateTimeNow(),"%d") This piece gets me a number for the current day of 15 (for today)

 

I'm not sure how I can combine these two separate formula strings while also incorporating the $200,000 monthly rate without receiving the following error: "trying to apply a numeric operator to a string value". 

If anyone has any suggestions on a formula string that could work, I would greatly appreciate the feedback!

 

Thanks in advance!

Frank

 

4 REPLIES 4
KP_DML
8 - Asteroid

Hello,

Use your functions and convert them to numbers, then you can perform your calculation:

 

200000 /
(
ToNumber(DateTimeFormat(DateTimeLastOfMonth(),"%d"))
*
ToNumber(DateTimeFormat(DateTimeNow(),"%d"))
)

rfoster7
9 - Comet

Also, in case you didn't know, you can reference earlier columns made with a formula tool with formulas lower down the chain. So you could do something like this: 

 

Capture.PNG

 

 

Just be sure you check your data types in your formulas so that you have them in the right numeric format. 

FrankB724
6 - Meteoroid

Thank you, @KP_DML ! I did have to remove one set of the parenthesis, but your formula helped me achieve my answer! I had to remove the ( after the / sign and then remove one of the ) at the very end. Your formula got me an answer of $403.26 this morning, but the answer should have been 103,225.81. After I modified the parenthesis, it worked perfectly! 

Thank you so much for the reply!

-Frank 

FrankB724
6 - Meteoroid

Thank you for the reply, @rfoster7 ! I wanted to try to keep it in one formula column if possible. Your solution would have gotten me there in the end as well!

 

Thanks!

-Frank

Labels