Calculation help

[Peter Frodell]

 

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)

[comment]

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 March 18, 2016 by comment

[ggt667]

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.

...

Edited March 20, 2016 by Josh Ormond
Remove irrelevant info.

[comment]

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:

[ggt667]

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 March 20, 2016 by ggt667

[comment]

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 March 20, 2016 by comment

[ggt667]

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.

[comment]

18 minutes ago, ggt667 said:

I was suffering from similar issues.

I don't think you are entitled to make such claim when - by your own admission - you don't know what OP's issue is.

[ggt667]

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 March 20, 2016 by ggt667

[comment]

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.

[ggt667]

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 March 20, 2016 by ggt667

[comment]

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.

[ggt667]

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 March 20, 2016 by ggt667

[Peter Frodell]

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;

[ggt667]

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 March 20, 2016 by ggt667

[comment]

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.

[Josh Ormond]

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;

[Peter Frodell]

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 

[comment]

@Peter Frodell

This is not helping. Please provide an exact description of the fields involved. Or post a simple file demonstrating the problem - as suggested by Josh.

[Josh Ormond]

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.

[Lee Smith]

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

[Peter Frodell]

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") 

[comment]

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 March 21, 2016 by comment

[Wim Decorte]

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") 

 

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?

[Peter Frodell]

No this is not the problem it´s emty, it can be as "comment" say shall try to change it too
 

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

[Peter Frodell]

1 hour ago, Peter Frodell said:

No this is not the problem it´s emty, it can be as "comment" say shall try to change it too
 

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

It worked!!! many thanks