Advice around date

Nick_AMS

**Clicked post too early! Title not editable and should read 'Advice around complex calculations involving date'**

Hi all!
Just getting started with building out a B2B SaaS concept I’ve been working on and - provisionally - landed on XANO + WeWeb as the route I’m looking to go down.

Product is essentially designed to automate the process of ‘fuel price indexing’ in the logistics industry, which ends up being a nasty manual process that's replicated 1000's of times by different involve parties in Excel each month.

Idea would be that the website allows you to create a policy with a set of 5-10 parameters and then share a URL with the suppliers who will ultimately invoicing you, with the relevant adjustment to include on the invoice automatically calcluated for them, alongside historic figures/future proejctions. 

Example of a manually calculated and published policy from a Swiss company here - https://tinyurl.com/bdhc5867 
Spent a few hours yesterday playing around getting the XANO database structure setup, but struggled with making any progress (at all) on the core calculation I’m looking for the product to be performing.
I’d have previously done this in SQL (modified version of query I’d have used for calculations shown below using DATEADD and LEAD functions), but doesn’t appear to be an option to do it in XANO as far as I understand.
I’m fully expecting to have to get in help for a number of parts of the build out, but hoping I can tackle a reasonable proportion of it myself and hoping I can at least understand how everything fits together for troubleshooting purposes in the future.
Full outline of what I’m looking to do below, but core questions would be-
• Is this the kind of task I can build through loops and math functions in XANO core interface or should I go be going down a different route (e.g. Lambda, though no Javascript / Lambda experience)?• If viable through loops/maths functions in the XANO core interface, any recommendations (or volunteers) of who could help guide me through?
• If better as a Lambda function (or other approach), is this something that would require any working knowledge of XANO or could I get someone who knows Javascript but doesn't know XANO to build it out with the variables I’ve specified and plug it in?
• If these kind of calculations are core to what the product is going to be offering, is XANO the right choice for the backend platform or could there be something eles that's a better fit? • Note: these calculations would need to be done ‘on the fly’ to display to users - reasonable to expect that they could be executed quickly (i.e. milliseconds rather than seconds)?


Outline of what I’m looking to solve

Two core tables relevant for this calculation -
• Contracts• Contains details of the policies that have been created in the system. Core fields include- • The fuel_price_index to use • e.g. Shell table referenced below
• Contract start/end date
• update_frequency • e.g. ‘month’ would determine that the adjustment would be monthly
• Lag_period • e.g. 2 would specify that it would be the fuel_price from 2 periods (e.g months) prior that would be used for the calculation
• policy_type • Three core policy types, all with similar calculations, but slightly different inputs
• Example below outlines ‘pcnt_adjustment’ use case
• fuel_share_pcnt • The % of overall cost assumed to be fuel, e.g. 25%
• baseline_fuel_price • The baseline fuel price set at the point of contract creation for fuel indexing purposes, e.g. Q4 2021 average = 1.12
• Various other constraints & adjustment factors (min / max adjustments, minimum change, rounding)
• Fuel_prices • Contains details of the fuel prices for a given week/month for a particular index (in a particular country), primarily specified in EUR_per_litre • e.g. Shell’s Monthly Germany Diesel Index https://www.shell.nl/consumenten/shell-fuels/historisch-prijzenoverzicht.html
• Table contains published actuals, as well as future calculated forecasts indicated by a boolean flag

[contracts table.png][fuel_prices table.png]
Output table required for the front-end (that will then be filtered and displayed as a graph) would contain-
A) Period
Fuel_price_in_period,
C) Fuel_price_used_for_calc,
D) Baseline_fuel_price,
E) Difference_pcnt,
F) Fuel_policy_adjustment
G) Actual / Forecast / Projection flag

Logic for calculation
• Get contract_id as a parameter from the front-end when a contract selected/page loaded, pass as variable • Pull update_frequency (e.g. month)
• Pull update_frequency_option (to distinguish between the methods for aggregating at  monthly/weekly level)
• Pull contract_start_date
• Pull contract_end_date
• Pull fuel_price_indeces_id (e.g. Shell DE Index)
• Choose periods to calculate: For each period listed in relevant column in fuel_prices table (column set by ‘update_frequency_option’, as fuel_prices table contains combination of weekly and monthly data) for the relevant fuel_price_indeces_id, run a loop to select the periods WHERE • period ≤ contract_end_date (sometimes null)
• period ≥ contract_start_date (sometimes null)
• Store this periods_to_display (will most commonly be a list of months) • Note: guessing this may be an unnecessary loop and can all be done in the core calculation
• Core calculation • For all periods_to_display, output • (A) Period
• (B) Fuel_price_in_period (value_EUR_per_litre from fuel_prices)
• (C) Fuel_price_used_for_calc, which is value_EUR_per_litre at (Period - (lag_period*update_frequency) • e.g. March 22 - (2*month) = Jan 2022  value_EUR_per_litre figure • Need to ensure it’s actual months (i.e. not 30.5 days, exact calendar month deduction, with all months set as 1st of the month in the database)
• (D) Baseline_fuel_price (baseline_price from contracts table)
• (E) Difference_pcnt (fuel_price_used_for_calc / Baseline_fuel_price -1)
• (F) Fuel_share_pcnt (Fuel_share_pcnt from contracts table)
• (G) Fuel_policy_adjustment, calc is • where policy_type = pcnt_adjustment • output = round(difference_pcnt*Fuel_share_pcnt, dp_rounding)
• max(output,min_adjustment) as ouptut 2 to ensure it’s not less than minimum adjustment allowed
• min(output2,max_adjustment) as output 3 to ensure it’s not more than maximum adjustment allowed
• case when abs(output3) < minimum_change_from_baseline then 0 else output3 end as Fuel_policy_adjustment
• Note: there are three policy_type options, each with slightly different calculation logics
• (H) Actual / Forecast / Projection taken from ‘is_actual’ boolean flag in fuel_prices and is based on Fuel_price_used_for_calc date (only one of the flags can be true) • Note: not included in SQL query shown below


Modified SQL query that I’d have run previously, using DATEADD and LEAD to do the core calc steps
Example parameters from contracts table to use
(A) update_frequency = month
lag_period = 2
(D) Baseline_fuel_price = 1.25
(F) Fuel_share_pcnt = 0.25
min_adjustment = -0.5
max_adjustment = 0.5
minimum_change_from_baseline = 0.01
Note: may be some minor errors in the below as not testing against a DB
select
A.fuel_price_indeces_id,
case when B.update_frequency = 'month' then A.month
when B.update_frequency = 'week' then A.week
when B.update_frequency = 'quarter' then A.quarter end as Period,
DATEADD(B.update_frequency, - lag_period, Period) as month_used_for_calc,
B.Baseline_fuel_price,
B.Fuel_pcnt_share,
LEAD(Fuel_price_in_period, 2) OVER (
ORDER BY
Period desc
) as Fuel_price_used_for_calc,
Fuel_price_used_for_calc / Baseline_fuel_price -1 as Difference_pcnt,
B.Fuel_share_pcnt * Difference_pcnt as output_pre_adjustment,
case when abs(output_pre_adjustment) < B.minimum_change_from_baseline then 0
when output_pre_adjustment > max_adjustment then max_adjustment
when output_pre_adjustment < min_adjustment then min_adjustment else output_pre_adjustment
end as pre_rounding_adjustment,
round(pre_rounding_adjustment, dp_rounding) as fuel_policy_adjustment

from
fuel_prices A
left join contracts B on A.fuel_price_indeces_id = B.fuel_price_indeces_id

where
1 = 1
and b.id = 123456
and month >= b.contract_start_date
and month <= b.contract_end_date

group by
xxxRealise that's a very, very long post.....any help or advice much appreciated!

Thanks,

Nick

Michael Udinski

 on quick glance I'd say everything could be done in the Xano function stack, you'll definitely have some nuance to work through. Math is actually better done through filters rather than the math functions in Xano.

If you're looking for some expert help, we can connect you with one of our partners. Just fill out this form.

Lambdas are certainly an alternative option. Users who know Javascript tend to use Lambdas to supercharge parts of their logic. But you should be able to do everything without code if you desire

Nick_AMS

Thanks  .

Was hoping to get slightly further than I have before engaging a partner/expert.

Any pointers as to which filters could be used to approximate a DATEADD or LEAD/LAG type function? 

Any examples you can point to that might get me started on replicating the calc.

Guess I could try posting this in the 'transforming data' group too....

Nick