Jump to content
Sign in to follow this  
Xaqtly

Subtraction result and Average field

Recommended Posts

Sorry subject isn't very descriptive, not enough room.

I've got a report that subtracts one date from another date, and puts the result in a very simple calculation field. It's basically just date_2 - date_1.

Additionally I've got a summary field in the footer that gets the average days from that calculation field.

My problem is when we get a row that only has one date added, and not both. When this happens I get a question mark in the calculation field, and apparently what Filemaker is doing is just plugging in the number 733533. At least that's what the field shows when I export it to Excel.

So obviously this completely throws my Average field off, as it starts showing things like -73554.6 days instead of maybe 12 or 15. My question is, is there a way to make the calculation field just show zero instead of 733533 when there's only one date instead of two? I really just want the summary field to ignore the calculation results from the "only one date" instances.

Share this post


Link to post
Share on other sites

apparently what Filemaker is doing is just plugging in the number 733533

No, it's subtracting from date_2, so the result is date_2 as a number (which is the number of days elapsed since 1/1/0001).

is there a way to make the calculation field just show zero

I don't think you want to do that, since these zeros will be included in the average. But you can easily make the result empty by changing your calc to =

Case ( date_1 and date_2 ; date_2 - date_1 )

Share this post


Link to post
Share on other sites

That seems to have worked perfectly, thanks a lot. If you don't mind though, would you mind explaining in noob speak why it worked? Like, what does the Case argument have to do with it? I'm still trying to figure out how calculations work.

Share this post


Link to post
Share on other sites

The Case() function is very similar to If(). Here, it tests if both dates exists, and if so it returns their difference. Otherwise there is no result (i.e. the field is left empty).

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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