Multiply a number to get a status
Hi ! How would it be possible to modify a loyalty status (which corresponds to a number) according to the sums spent.
Example: the threshold for changing loyalty level is every €600
 Case 1: a customer has spent €350, his loyalty level is therefore at 0 (by default).
 Case 2: a customer has spent €625, so his loyalty level must be 1 because he has exceeded the €600 threshold
 Case 3: a customer has spent €1470, his loyalty level must therefore be 2 because he has exceeded the levels of €600 then €1200
To achieve the calculation, I already have a formula field named "Chiffre d’affaires" which shows me the total amount spent by my customer. So I can start from there to calculate the loyalty level.
Is it possible for a formula to adapt the loyalty level (multiplier) by automatically calculating the ceiling every €600?
Thanks for your help
5 replies

You can use floor('Chiffre d’affaires'/600)

Sébastien Guillet said:
Can I reuse this formula to show me the Chinese sign of a client?Interesting question.
You can but just going by year is not accurate as the Chinese New Year typically falls around the end of January to mid February.
But a simplified method could be something like:
let y := year(Birthdate); switch true do case [1982, 1994, 2006, 2018][= y] > 0: "Dog" case [1983, 1995, 2007, 2019][= y] > 0: "Pig" end
A more accurate way would be to create two tables (Signs,NewYear). The Signs table would have 1 field called Animal. The NewYear table would be a child of Signs and have three fields (sDate and eDate) and a reference field to Signs (we can leave the default name Signs). The sDate and eDate fields would be the start and end date for the zodiac year.
Then in your other table you can create a formula field with something like:
let t := this; let getYear := (select NewYears where sDate <= t.Birthdate and eDate >= t.Birthdate); getYear.Signs.Animal
Content aside
 Status Answered
 10 days agoLast active
 5Replies
 56Views
 3 Following