Pie Chart Logic Issues

[JimmyLewis]

Hi,

I am quite new to filemaker and decided to try and build my own financial reporting system. I am having several issues, but right now my main problem is getting reports out of the data.

I am trying to build a Pie Chart in my "month sub-summary" part. The object is to take my fields:

p_date, Payee,

Amount,

Category,

Subcategory,

month/year (used for sorting)

kp_id (serial)

My major problem always falls when i want to get info based on a date range AND a category.

In this pie chart, labeled "Spending Per Category", I am trying to have the chart represent the percentage and amount spent that month in each category (the pie representing total spent that month).

I have tried various things, all failing. My current approach was to create calculation fields that add up each category in the body, for example:

If(category="Education";

Sum ( Amount ); 0)

then i created summary fields, put in the subsummary section, that sum up each individual category for that month. That seems to work.

so now that i have my monthly totals per category, how do i formulate the chart to reflect the results i want?

By the way, i am not a developer and can only assume that some of my logic can be cut in half, if not changed, so any suggestions on the thought process are more than welcome as well.

Thank you,

Jimmy

[comment]

See if this helps:

MonthlyPie.zip

[JimmyLewis]

Hi,

The chart sent to me has been of great help. Thank you for the time, a lot of it has been explained to me through your work.

There seems to be a problem though when I create my own based on your parameters and calculations. It seems that the graph shows all the results and percentages, however in the trailing sub-summary, similar to the one you created, it only shows me one category, as opposed to a summary list of all of them and their amounts by month, like it appears on yours.

I figured that this might have to do with the placement of the button (that triggers the script) and what it is reflecting (yours is in form view on single record, mine would be in list view, either in the body, sub by cmonth [leading or trailing], sub by category [leading or trailing]. I've obviously tried them all, with little success.

Do you know what might be causing this problem?

Also, the true usage for me here would be to be able to analyze my data so I know my spending, my spending vs. income, my expenses by category (excluding a few), etc.

Now, considering that there are so many different parameters:

(category, subcategory,month or range of months--still havent figured out how to do that),

and since i have exceptions

(income should not be in an expense report, neither should certain categories)

I am not quite sure what fields i can use to analyze this data.

For example, my category field is actually a calculation field that states: if subcategory="medical:doctors", "Medical" and so on for every different category. this is because i import my data from a cvs file with the category & subcategory are one and I want to sort by the category alone.

Now is there a better way for me to get partial information on a set of parameters without creating a new calculation field with a zillion if's? I can only imagine the possible errors this could cause in the future that I am not aware of. I know there is no such feature as "sum if", which could give me the monthly summaries of an amount total in a specific category.

Any ideas? Your help is appreciated not only by me but I am sure by all the others who will stumble upon this. Thanks.

Jimmy

Edited May 30, 2010 by Guest

[comment]

The placement of the button that triggers the script is irrelevant here. I don't know why you are having problems adapting the demo to your solution - perhaps you just need more time (for example, I am quite sure that there is no "form view on single record" in mine).

To include only certain types of records in the report (e.g. expenses, not income), start by performing a find.

my category field is actually a calculation field that states: if subcategory="medical:doctors", "Medical" and so on for every different category.

I believe it might be better to add a "decoding" table for this, e.g.

SUBCATEGORY	              CATEGORY

medical:doctors               Medical

medical:nurses                Medical

education:books               Education

etc.

and link the imported records to this table, matching on SubCategory.

Another option is to calculate the Category as =

LeftWords ( SubCategory ; 1 )

A lot here depends on how consistent the imported data is.

[JimmyLewis]

thanks, once again.

1) You said: "(for example, I am quite sure that there is no "form view on single record" in mine).

on both your layouts, transactions and report, they are in form view. Will that be the one making the difference?

-- I noticed that once I change the chart view to list view, it shows all the records and below it is the full description of each. Its just when in form view, displaying only the graph for the time requested, it shows the correct info on the graph but not underneath it. I will continue to work on it until i find a solution, but if you have any ideas they would help.

2) Your suggestion to convert my calculation field to a new table is exactly what i needed, still in excel logic. My categories are FIXED, meaning they wont have spelling mistakes, new categories, etc. the only thing is some of them are not categorized (in the calculation i stated to change that to uncatagorized). These categories will never change, however if one is added i would make sure to add it in the table as well.

When you say "link the imported records to this table, matching on SubCategory"-- how would that work? by creating a relationship between the two i would insert the field "category" from our new table to the other layouts to receive the category?

you also mentioned:

Another option is to calculate the Category as =

LeftWords ( SubCategory ; 1 )

is this taking the first word to the left from the field "subcategory" and dumping the rest? and what does the ";1)" stand for? What if i have a category called "one time purchasing"? this will only extract the "one" no? My subcategories are always separated by a ":", i.e. "medical:doctor".

As far as the consistency of my import, they come from a financial program and are consistent. I don't plan to import too often, only once every few months, and the fields like category will always be the same as they are predefined.

Regards,

Jimmy

[comment]

both your layouts, transactions and report, they are in form view.

I don't think so. The Transactions layout is in Table view - though it CAN be in Form view, if you wish. The Report layout is - and MUST be - in List view. Note also that this layout has two sub-summary parts and no body part.

When you say "link the imported records to this table, matching on SubCategory"-- how would that work? by creating a relationship between the two i would insert the field "category" from our new table to the other layouts to receive the category?

Yes.

What if i have a category called "one time purchasing"?

Then it's not "consistent" with the (single) example we had before (at least not with the rule I assumed from it). If the category is always the part before the first ":" you can extract it by =

GetValue ( Substitute ( SubCategory ; ":" ; ¶ ) ; 1 )

[JimmyLewis]

After reviewing all that you have given me here I just wanted to say thank you for your help, it is really appreciated.