Jump to content

Calculation help


This topic is 2294 days old. Please don't post here. Open a new topic instead.

Recommended Posts

 

Want to figure out provision if the field contains various amounts , eg 850 kr = 0,10 % but if the score is 0 , the answer is ? it gives wrong summary

if value is 0 its get ? and the result is row 1 ( *,1;)

how to get ? to zero
i have this now

Case (
Omsättning per timme ≥850 ; Summa *,1;
 Omsättning per timme  ≥ 800 ; Summa *,0807;
 Omsättning per timme  ≥ 750 ; Summa *,0588;
 Omsättning per timme  ≥ 700 ; Summa *,0338;
 Omsättning per timme  ≥ 650 ; Summa *,005;
 Omsättning per timme  ≥  600; Summa *0;
Omsättning per timme   ≤ 0  and  600; Summa *0;0)

Link to comment
Share on other sites

I am not able to reproduce the problem. Are you sure that is your exact calculation?

Note that the expression Omsättning per timme   ≤ 0  and  600 makes very little sense -  but it would not cause an error.

And I couldn't understand what you mean by:

43 minutes ago, Peter Frodell said:

the result is row 1 ( *,1;)

If the result is ? (an error), how can it be row 1 ( *,1;).

 

---
BTW, I am not sure what this calculation is about, but it seems like it should be a lookup instead. Otherwise any future changes in the threshold and/or percentage values will affect existing records.

 

 

Edited by comment
Link to comment
Share on other sites

Omsättning per timme = hourlyTurnover

Summa = sum

All calculations appears to be for the same record as where the calculation is run.

Case(
  a ≥ 850;
    b * 0.1;
  a ≥ 800;
    b * 0.0807;
  a ≥ 750;
    b * 0.0588;
  a ≥ 700;
    b * 0.0338;
  a ≥ 650;
    b * 0.005;
  a ≥ 600;
    b * 0;
  a < 600;
    b * 0;
    0
)
The problem is probably in the input data from what I can see, or the output, is your output field wide enough to show its content?

b * 0 is a bit weird in my head, maybe just 0 will do?

Also using , as decimal separator in a calculation is weird, comma as a separator should only be a formatting choice. As comma in math means and like( 250, 3.4, 120 ) as coordinates x, y, and z

On March 18, 2016 at 7:44 PM, comment said:

Note that the expression Omsättning per timme   ≤ 0  and  600 makes very little sense -  but it would not cause an error.

...

K1FsMGxM.jpeg

Edited by Josh Ormond
Remove irrelevant info.
Link to comment
Share on other sites

22 hours ago, ggt667 said:

Also using , as decimal separator in a calculation is weird, comma as a separator should only be a formatting choice. As comma in math means and like( 250, 3.4, 120 ) as coordinates x, y, and z

That is grossly incorrect: the decimal separator can be either a dot or a comma (or even another character), depending on your system settings.
https://en.wikipedia.org/wiki/Decimal_mark

If your file expects a comma as the decimal separator - either because it was created on a system using comma as the decimal separator, or opened on such system and set to use the current system settings in File Options - then you must use a comma as the decimal separator in a calculation. Otherwise you will get a syntax error, and you won't be able to close the calculation window:

se.png.e2c47f1251b7f89d772298d5f7fee8b9.

Link to comment
Share on other sites

In which is true if you are stuck with someone else's solution, however what I'm saying is a general code of conduct / preferred global approach to avoid errors when copy / pasting calculations and scripts between solutions.

By saying that I say I'm appreciating and easier life running one standard opposed to keeping track of which solution uses what standard.

It's much easier, at least for me. to have programming done with the mathematical language using "." as decimal separator in all calculations regardless of which solution I'm working on, and leaving all kinds of weird language/standards to the layouts. Like formatting whatever according to the language at hand. Such an approach is much more translateable to whatever. Especially when you also look at the thousand separator; in which could be comma, period, space, genetive symbol, single quote, and a couple of others, while decimal separator usually is space, comma, or period, and other region / language specific oddities; in which is non functional and 100% cosmetic.

Edited by ggt667
Link to comment
Share on other sites

I will be not dragged into this, as it has nothing to do with OP's issue.

OP's file is obviously using a comma as the decimal separator. They are not "stuck with someone else's solution". Apparently they are Swedish, their system is Swedish and presumably they prefer to conform to their own standard. That's a perfectly legitimate choice. There is no "general code of conduct / preferred global approach" with regard to decimal/thousands separators, as there is no general standard regarding date formats.

Most importantly, changing the system to use decimal dot will not solve their problem.

 

Edited by comment
Link to comment
Share on other sites

18 minutes ago, comment said:

OP's file is obviously using a comma as the decimal separator. They are not "stuck with someone else's solution". Apparently they are Swedish, their system is Swedish and presumably they prefer to conform to their own standard. That's a perfectly legitimate choice. There is no "general code of conduct / preferred global approach" with regard to decimal/thousands separators, as there is no general standard regarding date formats.

Half of the solutions I have been working on did too; as I'm Norwegian and until I got rid of that way of thinking I was suffering from similar issues.

Link to comment
Share on other sites

Well, at least before I give any suggestions I build my own solution based on the topc at hand and submit screenshots as pr the topic's header.

For all I know that may or may not have already solved the issue.

Further to the point I give suggestions how to avoid such issues as pr my own personal experience.

Edited by ggt667
Link to comment
Share on other sites

Have you been able to reproduce the problem? You will not know what the problem is until you can reproduce it. And you will not know what causes the problem until you can show that removing it makes the problem go away, and restoring it brings the problem back.

Until you do that, all your suggestions are no more than guesses. And IMHO not very good ones (except the one about the field not being wide enough). If you like, I can post a file where OP's calculation, as posted, works perfectly fine - decimal comma and everything.

Link to comment
Share on other sites

The symptoms described are within the boundaries of my own Filemaker experience, I can copied the table to a new file and posted it here, perhaps the problem no longer exists my example taken into consideration.

99356-calculation-help.zip

99356-calculation-help.xz

Edited by ggt667
Link to comment
Share on other sites

You're evading the issue. I am telling you again: I can prove that decimal comma is not the problem here, by showing the original calculation working. Your showing that another calculation works too does not prove anything and is a complete waste of time.

Link to comment
Share on other sites

18 minutes ago, comment said:

I can prove that decimal comma is not the problem here.

In theory I agree with you on that. Did you ever work with FileMaker using a different language than the default one?

Edited by ggt667
Link to comment
Share on other sites

Try to explain again, the field that holds this value is a summary field ( ex 850), if the calculation to that field is emtry the field that pick this up is (?) due to nothing to calculate, 

in that case I want the result to be 0, today it pick up the first row   Omsättning per timme ≥850 ; Summa *,1;

Link to comment
Share on other sites

Is the problem still in my example file? Can you make a subset of your solution that we can look at? Or let us login to?

Edited by ggt667
Link to comment
Share on other sites

32 minutes ago, Peter Frodell said:

Try to explain again, the field that holds this value is a summary field ( ex 850), if the calculation to that field is emtry the field that pick this up is (?) due to nothing to calculate, 

I am afraid that's not clear enough to reproduce the problem.

Link to comment
Share on other sites

Afraid I'm having difficulty following the issue also. Could you recreate the issue in a very simple file, so we can see what is going on?

What you have described so far would not cause the '?' result.

2 hours ago, Peter Frodell said:

Try to explain again, the field that holds this value is a summary field ( ex 850), if the calculation to that field is emtry the field that pick this up is (?) due to nothing to calculate, 

in that case I want the result to be 0, today it pick up the first row   Omsättning per timme ≥850 ; Summa *,1;

Link to comment
Share on other sites

51 minutes ago, Peter Frodell said:

This field is sum of another field ( hour worked) but if the hour not filled in this new field return the result is ? 
This can´t my "case" sqript manage to manage 

Trying really hard to understand, but we really need to know, in non-tech terms what you are trying to do. A list of the fields involved and what they do. And a sample file. I am not able to reproduce the behavior you are getting.

Link to comment
Share on other sites

Hi Peter,

I can’t believe the length of the discussion over a total or sum of hours worked.

I agree with Josh, a copy of the file would simplify things. Please read or reread this topic Anatomy of a Good Topic 

If you have any questions about Anatomy of a Good Topic, let me know via Private Message.

Lee

Link to comment
Share on other sites

Hi, sorry for my bad explanation, can´t send you a sample right now it´s a live very big part of a relationsship.

But I am trying again,

Fild 1. 
calculation field (timesummary) 
"mantimmar orderkort" Round ( Summa timmar  ;24 ) 

Field 2. 
calculation field ( numeric summary)
"Omsättning per timme"
Summa / Mantimmar ordkort*3600 

Field 3.
calculation field ( numeric summary)
"Oms sum provision"
Case (
Omsättning per timme ≥850 ; Summa *,1;
 Omsättning per timme  ≥ 800 ; Summa *,0807;
 Omsättning per timme  ≥ 750 ; Summa *,0588;
 Omsättning per timme  ≥ 700 ; Summa *,0338;
 Omsättning per timme  ≥ 650 ; Summa *,005;
 Omsättning per timme  ≥  600; Summa *0;
Omsättning per timme   ≤ 600; Summa *0;0)

If field 1 not have any data the result in field 2 that moves to field 3 is ?, see pict
how can I make the result in field 3 be "0" if field 1 is emty, today the result as you can see in the pict is from row one in the case, it should bee 0
( the first ? in the pict is field "Omsättning per timme") 

calc.JPG

Link to comment
Share on other sites

38 minutes ago, Peter Frodell said:

If field 1 not have any data

If field 1 ("mantimmar orderkort") does not have any data, then Field 2 ("Omsättning per timme") attempts to divide by zero:

Summa / Mantimmar ordkort*3600 

which will produce an error. And (for reasons unknown to me) an error is larger than any number, so your first test returns true and you get the result of Summa *,1.

 

To avoid the problem, try defining "Omsättning per timme" as =

If ( Mantimmar ordkort ; Summa / Mantimmar ordkort * 3600 )

There may be a better solution, but I do not see the entire picture.

 

---
P.S.  Round ( Summa timmar  ;24 ) probably does not do what you think it does.

 

 

Edited by comment
Link to comment
Share on other sites

1 hour ago, Peter Frodell said:

 

If field 1 not have any data the result in field 2 that moves to field 3 is ?, see pict
how can I make the result in field 3 be "0" if field 1 is emty, today the result as you can see in the pict is from row one in the case, it should bee 0
( the first ? in the pict is field "Omsättning per timme") 

calc.JPG

 

This may be something that was said way back at the beginning: that the field on the layout is not big enough to show the whole value.

If you look at the field in the data viewer, what is the result?

Link to comment
Share on other sites

This topic is 2294 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.