Math is not mathing (due to improper actions/steps?)

[KayKay]

Hello All

I’m having an issue solving a math/action problem, specifically when to create the formula. I need to calculate 2 formula’s to create a budget for Categories chosen by the user.
Formulas:
[1] Calculate the budget for each Service Type (ST) using preset %s.
[2] Calculate the budget for each Category using [1] using preset %s.

My issue is that in order to calculate [1] and therefore [2], I need the sum of all the chosen ST %s which I can find. However, when to create this calculation has me stumped.

• If I calculate [1] when a user picks a Category, the only correct calculation is the last chosen Category because all the ST’s have been chosen.
• If I sum the ST %s every time a Category is picked, the sum seems to never be correct i.e. 0+ current ST %
• If I create the formula when moving to the next screen, Adalo doesn’t seem to know which ST to calculate for.

How do I create a calculation loop for the different ST’s after a user has picked all their Categories in order to create an accurate amount? Here are some screenshots for context. Please help! TIA

STEP1: User picks a Category (Simple List) organised by Service Type (Custom List)

STEP2: Category is created where I initially created the calculation for [1]

ADDITIONAL1: Where I have hardcoded the formula in a label which is correct on another later screen.
Formula [1]:
Current Service Type > serviceBudgetRatio / Current Custom Event > Cust. Event Categories > Service Types > serviceBudgetRatio > Sum

ADDITIONAL2: Database for Category where I have attempted to do the calculations

[Rozza]

Not mathing

I’ll take a look later but think @Victor will relish this.

[KayKay]

Thank you @Rozza! I really had no other way to describe it

Hopefully @Victor can help too

[Victor]

Hi @KayKay,

Could you please provide a couple of examples what would you like to achieve? I am not sure I fully understand your issue.

Best,
Victor.

[KayKay]

Hi @Victor

I am trying to calculate two things:

1. Service Type budget
   based on the number of Service Types chosen by the user with the following formula:
   current Category’s (serviceBudgetRatio/sum of all serviceBudgetRatio’s) x eventBudgetwithFees

2. Category Budget
   based on the calculation above for the Service Type that the category belongs to with he following formula:
   (categBudgetRatio/sum of all categBudgetRatio within current Service Type) x Service Type budget calculated in 1.

Here is an example of the issue I am having:

1. If I calculate [1] when a user picks a Category, the only correct calculation is the last chosen Category because all the ST’s have been chosen:

Calculation used when creating the category the user has chosen

• Calculating ST Budget in a new update for the New Category within the submit form button

• Calculating Category budget in a new update for the New Category within the submit form button
  

  
  
  
  

• Results of calculation
  

  
  
  
  

The budget should be

1. Beauty & Styling - R4 400.00
   Hair Stylists (R2 444.44)
   Makeup Artists (R1 955.56)

2. Décor - R8,800.00
   Decorators (R8,800.00)

[Victor]

@KayKay,

Maybe I did not express myself in a clear way in my previous message. Let me rephrase.

Could you please provide a case for which you would like to calculate budgets, and the principle how these bugdets should be calculated? There is no need to provide Adalo screenshots for now. In order to give a meaningful advise it would be great to understand the general logic of budgets calculation.

Without such example, it is very difficult to understand what you are trying to achieve.
E.g.: (serviceBudgetRatio/sum of all serviceBudgetRatio’s) x eventBudgetwithFees - what is serviceBudgetRatio? why do you divide it by sum of all ratios? Is ratio a number between 0 an 1, is it a percentage, is it just a price value? why do you multiply it by eventBudgetWithFees - what does that mean?
You have provided the result of the budget which should be - but how did you calculate this “should be”?

Best,
Victor.

[KayKay]

@Victor Oh, got it!

So the logic for the calculation is to use the entirety of a user created budget (with fees deducted) whilst still respecting the ratios (%) for each Category/ Service type which I have created and is preset. The steps are as follows:

• The user creates a budget for the event, I then deduct fees (contingency & admin) which then creates eventBudgetWithFees.

• The user then is able to select categories relevant to their event. Each category has a preset ratio (%). Each ST also has a preset %.

• Every time a category is selected, the sum of all ST % changes provided the category (and thus its ST) chosen has not already been selected changing the calculation

For example, I created this table to check my calculations:

eventBudgetwithFees			R13,200.00
Service Type	ST %	Sum of all ST %	(1)= current ST % divide by Sum of all ST %	ST Budget = BudgetwFees x (1)	Category	Cat %	Sum of Cat % in current ST	(3)= current Cat % divide by Sum of Cat % in current ST	Category Budget = ST Budget * (3)
Beauty & Styling	5	15	0.33	R4,400.00			45
			(i.e. 5/15)	(i.e. 13.2k*0.67)	Hair Stylists	25	25/45=	0.56	R2,444.44
					Makeup Artists	20	*20/45=	*0.44	R1,955.56
Décor	10	15	0.67	R8,800.00			30
			(i.e. 10/15)	(i.e. 13.2k0.67)	Decorators	30	30/30=	1.00	R8,800.00

My problem is when to create these calculation as they are each dependent on the number of ST’s or categories chosen.

Thank you for your patience! I’m happy to send you the link to the app to clone & my calculations if I haven’t made things clearer.

[Victor]

Hi @KayKay,

Well, this took quite a bit of time to investigate.

If I understood you correctly.

PT1. You have some kind of list Service Types and Categories, Categories are a part of Service Types. They represent the services which can be offered to a client.
BTW the naming is a bit misleading so I decided change it a bit: in my example below I will use Types and Functions (which are part of a Type).
So there is a full list of all Types with Functions, it can look like this:

Also you have ratios for each type. They show how many %% of a total budget can be spent for each Type. E.g. in a full list (like above), only 30% can be spent on Beauty type.

Inside the Types you have Functions (a detailed activities for a type). These Functions also have ratios, which mean that only certain %% of a Type’s budget can be allocated to this Function. E.g. for a full list, in a Beauty type, only 50% of a Beauty budget can be allocated to Spa procedures.

Q1: is my understanding correct?

PT2. What should happen if only some of the Types are ordered, and only some Functions in these Types are ordered.

As I understood, the ratios should be recalculated using the following logic:

• ratios for Types should be recalculated and normalized based only on Types included in the order.
  E.g. in the example above, if we are not using Decor at all, the resulting ratios for Beauty and Food will be: Beauty = 0.4, Food = 0.6. They are calculated as:
  actualBeautyRatio = baseBeautyRatio / (baseBeautyRatio + baseFoodRatio) = 0.3 / (0.3+0.45) = 0.4.
• ratios for Functions inside types should be recalculated using similar logic - using “base ratios” only of those Functions which are selected.
• (important) budget calculation is a 2-step process: first the budget is allocated for each Type (using calculated Type ratios), and then in each Type the budget is split between selected Functions.

This is an example spreadsheet with only some Types and Functions selected:

PT3. So in order to calculate the Function’s ratio of a total budget, you will need to use the formulas:

actualCurrentTypeRatio = baseCurrentTypeRatio / sum (all selectedTypeRatios)
actualCurrentFunctionRatio = baseCurrentFunctionRatio / sum (all selectedFunctionRatios)
functionRatio=actualCurrentFunctionRatio * actualCurrentFunctionRatio

In the example above, in order to calculate how much %% of the budget client will spend on Hairdresser, you need to calculate:
actualHairdresserRatio = 0.3333 * ( 0.3333 + 0.16666) = 0.66666
actualBeautyRatio = 0.3 / (0.3+0.45) = 0.4
functionRatio = 0.2666666666.
So the client will spend approx. 26.6% of the budget on Hairdresser.

The problem is that you cannot just store this calculated number in the collection record easily.
Both ratios and amounts for ALL records may need to be recalculated EVERY TIME when you add or remove a record.

I’ve created a sample app and here is the video description how ratios and amounts change: Loom | Free Screen & Video Recording Software | Loom.

Best,
Victor.

[KayKay]

Hello @Victor!

Thank you so much for the time you’ve put into this, I truly truly appreciate it.

@Victor

Q1: is my understanding correct?

Yes, your understanding of the issue is correct. I realise now I explained terribly…

@Victor

PT2. What should happen if only some of the Types are ordered, and only some Functions in these Types are ordered.

I have been tinkering with this for a bit, I have managed to get this calculation correctly by first letting a user select the Types, thereafter a list of Functions would show afterwards of these selections. However the user experience would be terrible.

But then I got stuck again as I could not figure out when/how to try calculate:
actualCurrentFunctionRatio = baseCurrentFunctionRatio / sum (all selectedFunctionRatios)
functionRatio=actualCurrentFunctionRatio * actualCurrentFunctionRatio

@Victor

I’ve created a sample app and here is the video description how ratios and amounts change: Loom | Free Screen & Video Recording Software | Loom .

However, after looking at your sample app, I think it has solved what my actual problem was which was variability of the calculation based on selections.

“By creating the record or deleting the record from the database, from the structure, you have to recalculate the ratios and the final amounts into several records.” This is exactly the logic I need!!
Would it be possible to get access to the sample app you created?

[Victor]

Hi @KayKay,

Just in case, let me re-emphasize: you can’t store this calculated number directly to the collection at the moment of record creation.
The number is calculated dynamically with a custom formula and you need to implement some additional way to save the results (manually / countdown / 3rd party integrations).

I’ve sent you a DM.

Best,
Victor.

[KayKay]

Hi @Victor

I understand, I think I can figure out how to manage it from here. I needed to see it could be done somehow.

Thank you very very much, may you always finding the best & shadiest parking space.