Subtract 1 from target value every n days

[smallville3100]

Hi all,

I have a target number, say 10, and I want to subtract 1 value from this every 30 days (as an example). 

I have two dates, one is a historic date, say, 01/06/2022, and the second is the date as of today (the day you run the flow). 

The difference between the two dates might be 45 days.

Therefore, I need a workflow which will subtract 1 from 10 because 45 days is past the 30 day period, but not subtract two until it reaches 60 days. I don't know if there's a clever way to have this calculation run in an 'nth term', rather than writing it out like an IF statement where I need to specify the range, i.e. 0-30, 31-60, because my range could go on doing that for 30 more times, I don't know. 

For this example, we'll keep the number of days as 30 in all increments.

I can't seem to build this properly... The logic in the formula tool and DateTimeDiff aren't working for me at present - Hence why I don't any workflow to show.

Apologises, I'm a novice in execution, but have watched all the training videos so should understand the responses somewhat! :)

Many thanks for anyone who can help!!!!

[DataNath · Accepted solution]

Hey @smallville3100, how does this look? I've kept all steps of the Formula split out so you can easily track what's going on, but the workflow:

1) Adds a new field with Today's date

2) Calculates the difference between today's date and the provided date

3) Divides this by your n time period (in this case 30 days) and floors this so that days in between don't throw off the expression

4) Takes this number away from your target

Please let us know if this doesn't answer your question or needs building upon more - happy to assist!

[IraWatt]

Hey @smallville3100,

I would guess the DateTimeDiff is not working as it needs to be provided with a date data type 01/06/2022 is just text data. You can find some good quick videos on how data types work here on the community https://community.alteryx.com/t5/Interactive-Lessons/Understanding-Data-Types/ta-p/73958  

Here is one way you can fix this:

The date time tool converts your date to a date data type. Then I use a formula to minus off the difference between the two dates divided by 30 from a column containing 10.

The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20...

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[smallville3100]

Hi DataNath,

First of all, thank you so much for such a swift response!

Secondly, that was so clear in your instructions, and it's pleasing to know I had the correct steps for the first two, but I never thought to use the FLOOR element, I know this from Excel, but it never popped into my head.

Brilliant execution. Worked flawlessly. Thank you.

If I may trouble you for another one, but with a slight alteration. The formula I require is on the same premise, but with a slight tweak.

Let's say the target is 10, and the two dates are 01/06/2021 and today's date. However, I need the target to reduce by 1 after the first 6 months, by 2 after 12 months (another 6), and then by 1 every 12 months thereafter. As you can see, it's the first and second bit of the 'nth' value which is 6 months, and then the 3rd, 4th, and so on iteration are every 12 months.

Is there a way to work this one out as well?

[smallville3100]

Thank you IraWatt!

DataNath's solution worked as needed, but I do appreciate the extra help, and the community learning links you've supplied. Will give them a look. 

I can also see how my date can cause problems down the line so knowing the data time tool is a bonus!

[DataNath]

Hey @smallville3100 - no problem at all, happy to help and glad it was easy to follow!

Unfortunately I’ve just left my laptop so can’t build the flow out for you, but you can definitely achieve this with the same logic within an if statement to essentially handle any datediff in months under 12 months one way (-1 for each 6 months), before applying the other logic to any datediff beyond 12 months.