Peter Frodell Posted March 18, 2016 Share Posted March 18, 2016 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 More sharing options...
comment Posted March 18, 2016 Share Posted March 18, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
ggt667 Posted March 19, 2016 Share Posted March 19, 2016 (edited) 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. Link to comment Share on other sites More sharing options...
comment Posted March 20, 2016 Share Posted March 20, 2016 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: Link to comment Share on other sites More sharing options...
ggt667 Posted March 20, 2016 Share Posted March 20, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted March 20, 2016 Share Posted March 20, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
ggt667 Posted March 20, 2016 Share Posted March 20, 2016 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 More sharing options...
comment Posted March 20, 2016 Share Posted March 20, 2016 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. Link to comment Share on other sites More sharing options...
ggt667 Posted March 20, 2016 Share Posted March 20, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted March 20, 2016 Share Posted March 20, 2016 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 More sharing options...
ggt667 Posted March 20, 2016 Share Posted March 20, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted March 20, 2016 Share Posted March 20, 2016 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 More sharing options...
ggt667 Posted March 20, 2016 Share Posted March 20, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
Peter Frodell Posted March 20, 2016 Author Share Posted March 20, 2016 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 More sharing options...
ggt667 Posted March 20, 2016 Share Posted March 20, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted March 20, 2016 Share Posted March 20, 2016 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 More sharing options...
Josh Ormond Posted March 20, 2016 Share Posted March 20, 2016 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 More sharing options...
Peter Frodell Posted March 20, 2016 Author Share Posted March 20, 2016 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 Link to comment Share on other sites More sharing options...
comment Posted March 20, 2016 Share Posted March 20, 2016 @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. Link to comment Share on other sites More sharing options...
Josh Ormond Posted March 20, 2016 Share Posted March 20, 2016 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 More sharing options...
Lee Smith Posted March 20, 2016 Share Posted March 20, 2016 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 More sharing options...
Peter Frodell Posted March 21, 2016 Author Share Posted March 21, 2016 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") Link to comment Share on other sites More sharing options...
comment Posted March 21, 2016 Share Posted March 21, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
Wim Decorte Posted March 21, 2016 Share Posted March 21, 2016 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? Link to comment Share on other sites More sharing options...
Peter Frodell Posted March 21, 2016 Author Share Posted March 21, 2016 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 ) Link to comment Share on other sites More sharing options...
Peter Frodell Posted March 21, 2016 Author Share Posted March 21, 2016 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 2959 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 accountSign in
Already have an account? Sign in here.
Sign In Now