Percent Calculation of Outline Over Total in Sheet and Over External Data

Greetings everyone.

I am a reluctant Filemaker user due to its complexity. After working with Pan X for about 2 weeks I have found it to be much more user-friendly for average users like me and I have been able to fulfill most of my needs. I am stuck, though, with the matter below.

I have my sheet summarized as follows:
image

The data contained in this sheet are all expense line items.

I need to accomplish two things:

a) Show the % that each outline total represents over the total amount

b) Divide each total of the outline levels by a certain fixed amount unrelated to this sheet, let’s call it “hours”. Hours is a single record with one number that I can obviously add to this sheet or put on a different one.

What is the best way to accomplish my goal?

Thank you for your help.

The Outline Level step does nothing but change how the data is displayed, it doesn’t actually change anything in the database. So the first three of your Outline Level steps ultimately have no effect, since each one simply changes the display. The end result is simply as if you had only the last step, and only the Sub_Account summaries will be displayed. You can then expand or collapse the summaries manually, as described on this help page.

You won’t be able to calculate the percentage you want with the Summarize & Analyze dialog, but it can be done with a procedure. Let’s start with a procedure that does what your analysis does. You could get this procedure using the procedure recorder, or just type it in.

field "Expense Type"
groupup
field "Main Categories"
groupup
field Account
groupup
field Sub_Account
groupup
field Amount
total
outlinelevel 1

I’ve left out the Sort Down step, I’ll discuss that further in a moment. I also left out all but the last outline level step.

To calculate the percentage, you’ll need to add another numeric field to your database, I’ll assume it is called Percentage. Then you will add four lines to the end of the code:

lastrecord
let grandtotal = Amount
field Percentage
formulafill Amount*100/grandtotal

The first line moves the active record to the last record of the database, which now contains the grand total. The second line copies the grand total amount into a new variable, which I’ve named grandtotal (it could be named anything, as long as you are consistent). The third line makes the Percentage field active, so we can perform a calculation. The final line actually performs this calculation for every visible record in the database, which in this case will include all of the summary records. If you moved these four lines up one line, so that they were between the total and outlinelevel statements, it would calculate the percentage of every transaction relative to the grand total.

Now, back to sorting. The way you have set it up it will sort the detail records within each Sub_Account. I’m guessing that what you actually want is to sort the summaries, If you want the Sub_Account summaries sorted within the Accounts, and those sorted within the Main Categories, and those between the Expense Types, and then those sorted down, you would need to add multiple sorts at the end of the code, like this:

field Amount
sortdown
outlinelevel 2
sortdown
outlinelevel 3
sortdown
outlinelevel 4
sortdown

So here is your complete program. I’ve also added a startdatabasechange statement at the top, so that you can use the Undo command after running this program, and also noshow and endnoshow statements which turn off the display while this code is running, so that you don’t have to watch it update the display over and over again as each step runs.

startdatabasechange "allrecords"
noshow
field "Expense Type"
groupup
field "Main Categories"
groupup
field Account
groupup
field Sub_Account
groupup
field Amount
total
outlinelevel 1
lastrecord
let grandtotal = Amount
field Percentage
formulafill Amount*100/grandtotal
field Amount
sortdown
outlinelevel 2
sortdown
outlinelevel 3
sortdown
outlinelevel 4
sortdown
endnoshow
showpage

Here are some help pages for topics used in this program.

Jim,

Thank you for your prompt response.

The sequence

field "Expense Type"
groupup
field "Main Categories"
groupup
field "Account"
groupup
field "Sub_Account"
groupup
field "Amount"
total
outlinelevel 1
lastrecord 
let grandtotal = Amount
field Percentage
formulafill Amount*100/grandtotal 

gives me a NaN error in the Percentage field and it creates many additional records that appear to be blank except for a zero in the amount field. I cannot get rid of such records by undoing. The Percentage field is Number(Float) field.

Is this procedure working with just selected records or the entire database?

Also, could you please comment on my second question

“b) Divide each total of the outline levels by a certain fixed amount unrelated to this sheet, let’s call it “hours”. Hours is a single record with one number that I can obviously add to this sheet or put on a different one.”

Please note that my database contains data for several years and “hours” are different for each year. I need to perform both calculations, i.e. % over subtotals and totals and per hour calculation, on a yearly basis.

Thank you.

Based on your original post I assumed that your database contained numeric data in the Amount field. The results you are describing would be consistent with the Amount field starting off containing only zeroes. NaN means “Not a Number” which is the result you get when you divide by zero. The additional records you describe are summary records. You can get rid of them by using the Remove All Summaries command. You can’t use the Undo command to get rid of them because you did not include the startdatabasechange statement at the top of the procedure, as shown in your last example.

As written, my examples would work on only selected data. If you wanted it to work with all data, you would need to add a selectall statement before the first group statement.

This would require much more complex code. Basically you would need to do group by year before doing the groups already being done, then afterwards select each year and perform the calculations over and over again. It could be done with a lot of work, but it is not the kind of task Panorama is designed for.

It is so.

I must be doing something wrong because I could not, and all amounts for all records went to zero after running the procedure.

Just curious: The way I achieve this now in Filemaker is by performing calculations on data on two related tables. Would this be an option in Pan X?

I agree you must be doing something wrong, but without seeing the database I have no idea what. If the procedure is actually as presented it would not set values in the Amount field to zero, and you would wind up with five levels of summary records.

It’s not obvious to me how you would have split up the data into related tables, so I can’t really say yes or no.

However, I did think of a way that this could be done in Panorama that is not quite as complicated as I was originally thinking. In fact, the code is only a few lines longer and eliminates the need for a variable! It will run a bit slower, but unless you have hundreds of thousands of records, you probably won’t notice.

You will need to add an integer field named Year to your database. Also, I’m assuming you have a field named Date that contains the dates for each transaction.

startdatabasechange "allrecords"
noshow
field Year
formulafill datepattern(Date,"YYYY")
groupup
field "Expense Type"
groupup
field "Main Categories"
groupup
field Account
groupup
field Sub_Account
groupup
    field Year
    propagate
field Amount
total
outlinelevel 1
field Percentage
formulafill Amount*100/lookup("","Year",Year,Amount,Amount,5)
field Amount
sortdown
outlinelevel 2
sortdown
outlinelevel 3
sortdown
outlinelevel 4
sortdown
endnoshow
showpage

This code relies on the lookup( functions rarely used ability to skip data records and look up information only from summary records. In fact, I’ll bet most of the readers here might have wondered why this feature even exists, now you know.

Now, since the previous code didn’t work for you due to some problem with the Amount field, this code surely won’t either. So whatever problem that is needs to be straightened out. Also, I don’t have a database to test this code with, so it’s possible there is a typo.

For year, I already have a field named “D_Exp Year”, which is an integer that contains just the 4-digit year.

I have created a second “year” field as integer.

So I have run the procedure below but I get a “Missing Right Parenthesis” error.

startdatabasechange “allrecords”
noshow
field “D_Exp Year”
formulafill datepattern(D_Exp Year,“YYYY”)
groupup
field “Expense Type”
groupup
field “Main Categories”
groupup
field Account
groupup
field Sub_Account
groupup
field “D_Exp Year”
propagate
field Amount
total
outlinelevel 1
field Percentage
formulafill Amount*100/lookup("",“Year”,Year,Amount,Amount,5)
field Amount
sortdown
outlinelevel 2
sortdown
outlinelevel 3
sortdown
outlinelevel 4
sortdown
endnoshow
showpage

:slightly_smiling_face:

The problem is the space in D_Exp Year. To Panorama, it looks like the break between one statement and the next. When you have spaces, or punctuation, or anything that might be mistaken for an operator in a field or variable name you need to enclose it in chevrons «». On a U.S. keyboard the left chevron is option-backslash, and the right chevron is shift-option-backslash.

Change formulafill datepattern(D_Exp Year,“YYYY”)
to formulafill datepattern(«D_Exp Year»,“YYYY”)

The field name needs to be in chevrons since it has a space in the name:

formulafill datepattern(«D_Exp Year»,“YYYY”)

I am sooooo happy this morning!
Worked like a charm. Much appreciated everyone.

So the task left for me is figuring out how to calculate the % of amounts over the number of hours. The solution may be inputting it manually on the reports by the looks of your comments because it is a simple division. It won’t look as I had hoped but life is imperfect anyway.

I have just discovered that, after running the procedure, the D_Exp Year has been set to 0 for all records (the same thing that happened yesterday with the amount field).

Hopefully this will give you an insight to the root of the problem.

Again, D_Exp Year is an Integer field.

The datepattern( function expects the field to be a date field. Since your field is a four digit integer, you will get your wanted result with a simple pattern( function.

Oh, wait: You do not need the pattern( function at all, because it changes the number to text, while you seem to want to keep the number.

Maybe you need to activate “Enable grouping for numeric fields” in Panorama X’s preferences.

KJM,

I have enabled grouping for numeric fields and run the procedure.

The result is the same: the D_Exp Year field is showing just zeros.

Just to clarify:

The procedure runs fine the first time around and I obtain the percentages I need.

The side effect is that the D_Exp Year is modified with zeros.

The problem is that you shouldn’t be running the formulafill at all. In Jim’s original code, he was extracting the year from the date in a field named Date, and putting it in a text field named Year.

In your version, you have an integer field named D_Exp Year which already contains the year, and you are writing into it with a formulafill. It already contains the year. There is no need to do anything to it.

The field command that precedes the formulafill is what designates which field gets filled. If the field already contains the values you want it to have, then you don’t want to fill it with something else.

In Panorama, a date field contains a number, called a Julian day number, which is the number of days since an arbitrary date a few thousand years ago. The datepattern( function will format that as a readable string of text. An integer like 2020 would correspond to a date a couple of thousand years BC. The datepattern( function doesn’t work with dates that early. That field got filled with zeroes because you tried to fill it with the results of a datepattern( function that was outputting a blank, because the number it was working on was less than the smallest number it can handle.

As I said before, this code contains an error, because your field “D_Exp Year” is not a date field. So the result of this function is 0, and later on the number 0 gets propagated into the summary lines.

Omit the datepattern( code line. You do not want to lose the existing data in this field, you just want to group it:

field “D_Exp Year”
groupup

The grouping creates summary lines for each year, and the propagate statement fills the summary line of the “D_Exp Year” field with the year:

field «D_Exp Year»
propagate

Kurt and Dave has posted the correct answer, but since I posted the code let me tie a bow on it and post the needed updates. Obviously I had no idea you already had a year field, so I assumed you would have to create one and have the code fill it in. Since that isn’t the case, the formulafill isn’t necessary and you just have to change the field names throughout the code.

startdatabasechange "allrecords"
noshow
field "D_Exp Year"
groupup
field "Expense Type"
groupup
field "Main Categories"
groupup
field Account
groupup
field Sub_Account
groupup
field "D_Exp Year"
propagate
field Amount
total
outlinelevel 1
field Percentage
formulafill Amount*100/lookup("",""D_Exp Year"",«D_Exp Year»,Amount,Amount,5)
field Amount
sortdown
outlinelevel 2
sortdown
outlinelevel 3
sortdown
outlinelevel 4
sortdown
endnoshow
showpage

All,

A minute before Jim posted, I had adopted the following solution:

I changed the D_Exp Year to an MM/DD/YYYY date field and I have run the following procedure:

startdatabasechange “allrecords”

noshow

field “Expense Type”

groupup

field “Main Categories”

groupup

field Account

groupup

field Sub_Account

groupup

field «D_Exp Year»

propagate

field Amount

total

outlinelevel 1

field Percentage

formulafill Amount*100/lookup("",“Year”,Year,Amount,Amount,5)

field Amount

sortdown

outlinelevel 2

sortdown

outlinelevel 3

sortdown

outlinelevel 4

sortdown

endnoshow

showpage

I have not finished testing but it seems to be working. So now I have two options to choose from. Since Pan X will allow to do some fine tuning to my database to make thins easier for me, I will end up using the one that best suits my needs in the end.

Everyone’s input is truly appreciated.