A running total, but not with a summary field?

[TysonB]

I'm trying to put together a calculation/graphing tool for a friend of mine, he has several fields that he needs a running total generated for.  Easy, right?  Just use a Summary field and click Running Total.  Except...  on some of those fields from which we are calculating the running total,  they have no data in them.  But the running total keeps going.  So what it looks like is something like this

Where the field on the left is the number, the field on the right is the running total.  What he wants is that running total (summary) field to be empty for the same records that have no data in them for the other field.  But there's additional records in his data set for his other fields.  Is there any way to do a running total calculation that is not a summary field?  Or am I going to have to break out all these different chunks of data into different tables?  I thought of that at the beginning, but it makes things infernally complicated for doing his later calculations and I haven't figured out how to get the running total to appear in  a portal properly.

Thanks for any thoughts you might have on this...

[Steve Martino]

Can't you just use 'Hide' and hide the summary field when there is nothing in the other field?

[TysonB]

That works for the display of the columns, but not for graphing.  What he gets for the graph, because there's an entry in the field, the same number for the rest of the rows, is a "flatline" across the remainder of the graph.

[comment]

Technically, you could define a calculation field along the lines of:

If ( Amount  ; sRunningTotal )

Whether this will fit your purpose is hard to say, because I don"t understand your purpose. You say;

1 hour ago, TysonB said:

 What he gets for the graph ... is a "flatline" across the remainder of the graph.

What does he want to get - and why? In my understanding, a flat line is the correct representation of a running total when there are no new amounts.

 

[TysonB]

What he wants, when there's no new amounts in the AC_KTR field (ie, there's no data in that field), for there to be no new amounts in the ACWP summary field (no data to be graphed).  So that the graph just stops.  What he's trying to do is graph four different running totals of four different data sets, each consisting of a monthly cost and a monthly running total.  One of them is a predicted set (what he expects to see, I didn't show that data), the second is the  predictions from his contractors,  the third is the actual set (the two columns I showed) and the fourth would be a revised prediction.  The actual amounts and running totals have the potential to end before the predicted set of records end, and the revised predictions would continue from where the actuals left off.  Here's the full sample data set he gave me (out of Excel), that I've imported into FileMaker

 

Would it be easier to do in Excel?  Yes....  But he has a whole raft of other analysis he wants to do that he wants to have in FileMaker, where he is currently tracking the progress of 50-some different projects (budgets, correspondence, contacts, expenses and funding, you name it, we're tracking it), so having this in there would make it pretty seamless.

[comment]

6 minutes ago, TysonB said:

So that the graph just stops. 

Well, then I believe my suggestion will work (much to my surprise, I should add).

 

[TysonB]

52 minutes ago, comment said:

Well, then I believe my suggestion will work (much to my surprise, I should add).

 

You are correct, sir!  Thanks very much!  Simple and elegant!  I will need to remember this one  for other purposes!

 

[LaRetta]

2 minutes ago, TysonB said:

 I will need to remember this one  for other purposes!

As shall I!

[comment]

Come to think of it, you don't need the calculation field.  You can perform the same calculation within the chart itself.

 

[TysonB]

True.  However, if I put the calculation field into his data table, then he gets a really good visual representation of when his actual data stops.  It neatens things up a treat in that list view.

[comment]

That could be achieved by hiding the summary field conditionally. True, having the calculation field has the advantage of placing all the required logic in one place. But I also like to eliminate fields as much as possible.

 

Edited February 17, 2020 by comment