LinkedIn
Twitter
Very good @Suvrutt_Gurjar !!
For the date format, the display would depend on the locale, but in expressions, you are forced to use a US-locale-like mm/dd format. So it should be 12/31 not 31/12.
You can see how much funny this is using the expression assistant, where you write in one locale but get the result in another!
I am posting this to show an alternative approach using a loop mechanism.
I am not sure which is a better approach but I tend to use this approach because expressions tend to be a bit simpler, less table maintenance (at least in this case) although you depend more on actions and a helper col used as a temporary value.
As someone mentioned, there definitely are more than one way to do the same.
I guess you probably need to know to use both approaches depending on the situation.
Here's how you do it.
Create a pen_rate table: Year (key), rate (daily rate)
Create a temporary col [cur_year] to hold the year to use in each iteration of the loop. This should be initialized to YEAR([fr_date])
Four actions.
1) Calculate the yearly penalty. This one calculates the year by year penalty and accumulates the result into the col [penalty]
[penalty] +
IFS(
[cur_year]=year([fr_date]), HOUR(date("12/31/" & [cur_year]) - [fr_date])/24 + 1,
[cur_year]=year([to_date]), HOUR([to_date] - date("01/01/" & [cur_year]))/24 + 1,
TRUE, HOUR(date("12/31/" & [cur_year]) - date("01/01/" & [cur_year]))/24 + 1
) * LOOKUP([cur_year],"pen_rate","year","rate")Somewhat similar to @Suvrutt_Gurjar 's case determination logic.
2) Count up the [cur_year]. Simply
[cur_year] + 13) Repeat step. This calls the 4) if
[cur_year] <= year([to_date])4) An action to execute the sequence of the above three
A few screen shots. Note, [memo] just writes out the result of each iteration. not required for production.
Hope this adds an additional tool in your tool box!
Hi @TeeSee1 ,
Thank you for your revert. Good that you found the comments useful and mostly agree. Intention was to only reader know the pros and cons.
You mentioned
Here, I politely mention and sorry to say that you have totally misquoted me. I sincerely request you not to do so. I have never said iterative approach is not innovative. In fact I started by saying that it is innovative way, but I believe it is not suited for this case.
I mentioned:
My only point was about using looping for mass calculations is not a fit case and other challenges that I pointed out.
Also I typically never respond on alternative approaches unless it is totally going in wrong direction, but you mentioned certain advantages over expression way and so felt compelled to elaborate for future reader.
