I have a field, ‘CRM Category’ (I.e. Diamond, Gold, Silver) for B2C End Customer – and another field for Total purchase over last 3 months – Calculation - If customer purchases for Rs 10,000 over last three months, a Diamond Customer, If customer purchases Rs5,000 over last three months, Gold customer. If customer purchases less than Rs5, 000, silver customer

 

My question is for the calculation of the Gold customer as for the Diamond i will use the grater symbol and for Silver the less symbol.

Case ( Total_Revenue  < "$10,000" ; "Diamond")

Thank for your help

"$10,000" is text, not a number. Any number will be higher than this, because the $ character precedes any digit in alphabetical order. Make sure you compare numbers to numbers.

Note also that the Case() function returns the first result whose test evaluates as true - so you need to mind the order of the tests. In your example, it could be either:

Case (
TotalPurchase ≥ 10000 ; "Diamond" ;
TotalPurchase ≥ 5000 ; "Gold" ;
"Silver" )

or:

Case (
TotalPurchase < 5000 ; "Silver" ;
TotalPurchase < 10000 ; "Gold" ;
"Diamond" )

 

 

What would be value above 10,000?

2 hours ago, Kishan_Canoo said:

What would be value above 10,000?

What do you mean?

8 hours ago, Kishan_Canoo said:

What would be value above 10,000?

In Comment's first example:

TotalPurchase ≥ 10000 ; "Diamond" ;

≥ is the symbol for 'greater than or equal to'

In Comment's second example:

values less than 10,000 are specified in the first two tests and the final 'default result' of  "Diamond" will be applied to all records NOT identified in the first two tests so all records 10,000 or greater will be Diamond.  Read up in FM Help on using the Case() logical function. 

Edited October 8, 20169 yr by LaRetta

Thank you all. That works as expected. My problem is solved now.