Running Totals

For HanDBase related topics that are not specific to one platform.

Running Totals

Postby junglism » Sun Oct 25, 2009 6:44 pm

i want to set up a really basic database to track my purchases in 5 different categories.
Is it possible to:
* generate/display a running total for purchases in each of those 5 categories
* same as above but only within current month?
* have this data presented in some kind of form, or.. as the first table the appears when i open up the software?

the guy in this thread is after something similar. a process of using filtering is described,.. but i dont know whether it will apply in this situation..
Posts: 3
Joined: Sun Sep 27, 2009 9:57 am

Re: Running Totals

Postby dhaupert » Mon Oct 26, 2009 9:28 am


Very interesting question! We don't have a 'summary screen' type of implementation but in theory, you could create a related table that has a single record in it, taking the running totals you keep from within the table of interest itself.

You can definitely keep running totals for each category since you have a hard-defined limit of 5 categories. The trick to do this is to use several fields for each categories total. The logic would be like this for each one:

Hideen Field: TempSubTotal is a Calculated Field = Previous Category Total + 0;
Hidden Field: IfCategoryx is a conditional field that if the current category field value is equal to the one you're adding, then the output is 1, otherwise, it's 0
Field: Category Total = tempsubtotal + IfCategoryX

So the first field takes the previous amount and adds 0 (the latter part is to make it a formula- a requirement of the calculated field)
The second field evaluates whether the category of the given field is the value you're trying to sum up. If it is, the output is 1, otherwise it's zero
The third field adds the above two together. If the category isn't a match, there will be no change from the previous record, otherwise, it will increase by one

You repeat this for each category value and hide the first 2 fields for each of them (by setting visible in Edit Record screen to no, and setting the list view pixels shown to 0), thus you''ll only see the totals for each category.

To do just the past month, you can create a view with the date field filtered to only show the previous month.

Hope this turns out to be useful for you!
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Return to HanDBase General Discussion

Who is online

Users browsing this forum: Bing [Bot] and 1 guest