cancel
Showing results for 
Search instead for 
Did you mean: 
bimi82
Nintex Newbie

Help with a complex calculation based on a few factors...

Jump to solution

Hello

 

I have created a request to loan an item which in return will show the [calculated value] to be paid by the user. However, the calculation varies depends on the pength of period for how much it was loaned for which should be pretty straight forward and easy, right?

Well... not so easy for me at least Smiley Happy
So, let me explain what the current situation is:

 

There are date fields:  [FromDate] and [UntilDate]

There is also a Formula field on the form which contains the calculation expression.

 

The criteria is: 

If the DateDifference between [FromDate] and [UntilDate] is above 21 days and within a month then show the MonthlyCost (which is a lookup field) otherwise calculate UsedHours * HourlyRate





However the downfall is; what if the user loands it for 35 days, or 45 days or 60 days ...

I am now confused to how to get to a conclusion where we say to charge the monthly rate + the remaining days ... for example:

YearlyRate = £4,856.31

MonthlyRate = £404.69
DailyRate = £19.20 (capt to 8hrs per day)
HourelyRate = £2.40 (this is used if the loan is belw 8 hrs within one day)

If a user loans it for 45 days, what is the total cost to be charged?

If we say, well 45 days - 21 days = 24days remaining

which makes 1 x MonthlyCost + (24 days x DailyRate) = 404.69 + (24 days x 19.20) 
// by this am I not overcharging the user, right? 

 

or is it rather 45 days - 30 days (because we say if above 21 days then charge for whole month) = 15 days remaining >> 1 x MonthlyCost + (15 days x DailyRate) = 404.69 + (15 x 19.20) right?

 

So, what about when loaning it for 60 days?
Well, then the user needs to pay for 2 x MonthlyCharge

 

... on and on and on ....

 

I need this to put it in a calculation utilising the Form Variables as well.
I have the variable to get charges if anything is over 21 days but the rest is difficult for me.

IF(dateDiffDays(FromDate,UntilDate) > 21, lookup("ListName","ItemName", parseLookup(Item), "MonthlyCost"), calculateDailyCost+calculateHourlyRate)

 

 

Labels: (1)
Tags (1)
0 Kudos
Reply
7 Replies
chaddavis
Nintex Newbie

Re: Help with a complex calculation based on a few factors...

Jump to solution

Update 2.0:

There was a bug in the regex that thew an error in Internet Explorer. I've fixed it now, and updated their references below.

 

Update:

You're definitely going to need to confirm the calculations, but, I think I have some pretty good progress here.

 

This example breaks everything out into separate steps. Once you've confirmed the calculations, we can work to combine everything into one.

 

2019-02-25_16-25-56.png2019-02-25_16-26-09.png

 

Now, I realize you have date picker fields and not a text field, but for testing, this worked really well.

Additionally, I added in the hours calculations because you mentioned the hourly rate, but I wasn't sure if you actually needed that.

 

So, the left column of Calculated Value controls finds the full months, days, and hours from the number in the text field. 


Hours: 

The simplest way to calculate the hours is to multiply the days value from the text field by 24, and then find the remainder after dividing by 24 by using the % symbol. (i.e. The remainder of 51.25 multiplied and then divided by 24 is .25, meaning one fourth of 24, which is 8.)

Calculation:

round((days*24)%24)

 

Days:

Take the days value from the text field and find the remainder after dividing by 30. (i.e. 51 % 30 = 21 because 30 goes into 51 once leaving 21 remaining) Then look for a decimal point and remove it and everything after it. (i.e. 21.25 days should show 21 days)

Calculation:

replace(days%30,"\\..*",'') 

 

Months:

Take the days value from the text field and divide it by 30. Then look for a decimal point and remove it and everything after it. (i.e. 1.7 months should show 1 month)

Calculation: 

replace(days/30,"\\..*",'') 

 

 

The middle column calculates the time that is charged based off what you mentioned above:

 

Hours:

If the hours are 8 or greater, show 0. Otherwise, show the hours.

Calculation:

If(hours<8,hours,0)

 

Days:

If the days are greater than 21 OR (hours are 8 or more AND remaining days plus one is greater than 21), show 0. Otherwise, if hours are less than 8, show the remaining days. If they're greater than 8, show remaining days plus 1.

Calculation:

If(or(remainingDays>21,and(hours>=8,remainingDays+1>21)),0,If(hours<8,remainingDays,remainingDays+1))

 

Months: 

If remaining days is greater than 21 OR (hours is greater than or equal to 8 AND remaining days plus one is greater than 21), show months plus one. Otherwise, just show months.

Calculation:

If(or(remainingDays>21,and(hours>=8,remainingDays+1>21)),months+1,months)

 

 

 

---------------------------------------

Use a combination of divide (/) and remainder (%).

 

In my example, I placed a text field on the form with a name of 'days'. 

Then I added a Calculated Value field next to it:

2019-02-22_15-24-14.png

 

Here is the formula in the Calculated Value field:

2019-02-22_15-24-51.png

*(regex is wrong in this image - should be "\\..*")

 

If you just use the divide operator, you'll be left with a decimal remainder, which you don't want. Instead, I use the replace() function with a little regex to remove the decimal and everything after it (because rounding won't work for this situation), and then later in the calculation I use the remainder operator to find the remaining days after the division.

 

This should help get you started in the calculation. If I have more time later, I'll see if I can make anymore progress on it.

Reply
bimi82
Nintex Newbie

Re: Help with a complex calculation based on a few factors...

Jump to solution

Wow, what a genius Smiley Happy
Luckily there are people out there that know maths better than me lol

Just to confirm the costings cos your result look a bit different:

The item costs annually: £4,856.31
which is then divided by 253 (working days) = 404.69 per month
then the 404.69 is divided by 253 (working days) to get the £19.19 per day
then the 19.19 is divided by the 8 (working hours) to get the hourly rate = 2.40

- if more than 8 hrs within a day then charge the daily charge
- if less than 8 hrs within a day then charge the hourly rate
- if more 21 days (but within a 30 days worth of a month) then charge the monthly rate
- *if more than a month then charge the MonthlyCost + the remaining days + Hours

 

* this means that if for example its booked for 50days and 12 hrs then it is 1 month worth of charge and then plus the remaining days + hours  == 1Month+(50 - 30)+Hours

 

Now what if 65days and 16hrs?
Well, in this case it is 2 x MonthlyCost + Remaining Days + Hours


Hope it makes sense ...

0 Kudos
Reply
chaddavis
Nintex Newbie

Re: Help with a complex calculation based on a few factors...

Jump to solution

Rather than just saying '2 x MonthlyCost + Remaining Days + Hours', please walk through the calcuations with actual numbers so we can proof the math.

 

Here's what I get in your example of 50 days and 12 hours:

2019-03-01_10-36-30.png

 

Breaking down the calculation:

  • 50.5 days is 50 days and 12 hours
  • 50 days contains 1 full month (30 days) with a remainder of 20 days
  • 12 hours is >= to 8 hours, meaning it should be converted to one full day
  • This means we have 1 full month and 21 days to charge
    • Since the requirements from your original post state that the days should only be converted to another full month when they're greater than 21 days, this does not meet that criteria
  • So, we take the 1 full month x the monthly rate of 404.69 and add it to the 21 days x the daily rate of 19.19
  • This gives us (1 x 404.69) + (21 x 19.19) -> 404.69 + 402.99 -> 807.68

 

Please double check these numbers with whomever is doing your actual charging to confirm the math is correct.

 

And your example of 65 days and 16 hours:

2019-03-01_10-37-00.png

 

Breaking down the calculation:

  • 65.66 days is 65 days and 16 hours
  • 65 days contains 2 full months (30 days x 2) with a remainder of 5 days
  • 16 hours is >= to 8 hours, meaning it should be converted to one full day
  • This means we have 2 full months and 6 days to charge
  • We take the 2 full months x the monthly rate of 404.69 and add it to the 6 days x the daily rate of 19.19
  • This gives us (2 x 404.69) + (6 x 19.19) -> 809.38 + 115.14 -> 924.52

 

Please double check these numbers with whomever is doing your actual charging to confirm the math is correct.

 

I did update the calculations for the monthly, daily, and hourly charges based off your last post. That should make the numbers more accurate.

 

I published my form as a Nintex Live Form so, feel free to test it out yourself:

https://ntx.lv/2BT0fHq

 

Now, once you've confirmed the math, there are still additional steps to go through.

 

First, my example is just a text field where you enter the days. You mentioned that you have date pickers. That means you'll have to actually run a calculation (probably datediff) to get the number of days and hours from those date pickers. If your hours are not in the format of being portion of one day (i.e. decimal), then the other calculations will need to be updated as well.

 

Secondly, you probably don't want your form to have all the calculated value fields and labels like mind does so, you'll need to get those calculations combined down into just a few fields. 

To calculate the montly, daily, and hourly charges, I used Form Variables. That may be a big help for you with the other calculations as well.

Reply
bimi82
Nintex Newbie

Re: Help with a complex calculation based on a few factors...

Jump to solution

There is an error when testing your LiveForm.

 

 

0 Kudos
Reply
chaddavis
Nintex Newbie

Re: Help with a complex calculation based on a few factors...

Jump to solution

Very interesting. I do the majority of my work in Chrome, and it works great in that browser. However, I did just try it in IE, and you're right, there's an error.


I'll look into that, but in the end, we still need you to confirm the calculations so, please test in Chrome for now.

0 Kudos
Reply
chaddavis
Nintex Newbie

Re: Help with a complex calculation based on a few factors...

Jump to solution

I found the bug and it's been fixed. The form should be able to be used in Chrome or IE now. Thanks for catching that.

0 Kudos
Reply
bimi82
Nintex Newbie

Re: Help with a complex calculation based on a few factors...

Jump to solution

the preview works Smiley Happy

Please leave the form link live so I can use it for demo any time.

 

The only thing I need to figure out is how to fit the total days/hours as you currently type in manually. Whereas in my case there is [FromDate + Time] and [UntilDate + Time] date fields which then shows how any days (diffDates) and how many hours (diffHour) and how many minutes.

 

 

0 Kudos
Reply