Charting forecast

[Rikki]

I have 2 tables.

First table is named "Financials" and is related to the customers database by the customer ID. In this table I have a field named "heating_bill".

Second table is named "CHART". In this table I have created 10 records and filled out the "YEAR" text field with a year like so: YEAR="Year 1", YEAR="Year 2", up to YEAR="Year 10". I have used the "YEAR" field for my horizontal (X) axis on the chart so that I have one bar per year showing, then in the vertical (Y) axis I have a calculation using case to show the projected heating bill year on year with an 8% increase every year. My calculation is like this:

Case(
CHART::YEAR="Year 1";CHART::Heating_bill;
CHART::YEAR="Year 2";(1.08)*CHART::Heating_bill;
CHART::YEAR="Year 3";(1.08^2)*CHART::Heating_bill;
CHART::YEAR="Year 4";(1.08^3)*CHART::Heating_bill;
CHART::YEAR="Year 5";(1.08^4)*CHART::Heating_bill;
CHART::YEAR="Year 6";(1.08^5)*CHART::Heating_bill;
CHART::YEAR="Year 7";(1.08^6)*CHART::Heating_bill;
CHART::YEAR="Year 8";(1.08^7)*CHART::Heating_bill;
CHART::YEAR="Year 9";(1.08^8)*CHART::Heating_bill;
CHART::YEAR="Year 10";(1.08^9)*CHART::Heating_bill;
)

Now I would like to do this another way that is more automated because with this design I can only do it manually by entering the current heating bill of the customer into the "CHART::Heating_bill" field and then the chart is generated.

I need it to work from a relationship but I cannot get my head around how I would need to do it because I need to show the 10 years, so the only way I could think of doing that is creating a record for each year in the chart.

Is there a better way that I can do this with a relationship and a calculation to make it more automated?

Regards

Rikki

Edited July 1, 2015 by Rikki

[comment]

I am a little confused by your description. What exactly does a record in the Financials table represent? It sounds like "the current heating bill of the customer" could be calculated - from the context of the Customers table - as:

Last ( Financials::heating_bill )

assuming that the records are entered in chronological order and that the relationship is not sorted otherwise.

[Rikki]

Sorry I forgot to mention 

Financials::heating_bill

Is the field where the data is stored and I would like to relate to this field from my CHART table. I am using the data in this field to manually enter it's value into the CHARTS::Heating_Bills field to generate the chart.

The Financials table stores data for another report for the heating bills but I don't know how to chart the data from this table how I would like, so I have created this new table called "CHARTS" because I couldn't think of another way to display one bar per year via the Financials table.

This is the result I want but the only way I could think of producing a chart like this is to have 10 records in a new table so that there is 1 bar per year for the 10 years I need to forecast.

Sorry, I am fairly new to Filemaker so it's probably something fundamental I am missing.

Edited July 1, 2015 by Rikki

[comment]

You haven't really answered my question. Presumably there are more than one records in Financials per customer; I am asking how one would recognize which one is the "current" one.

Note that you won't be able to do this from the Charts table at all, unless you select a customer first and store this selection in a global field in the Charts table. For that matter, I am not sure why you even need this Charts table in the first place; you could make all the necessary calculations in the Customers table.

Have a look at the attached example file (Parent == Customers; Chid == Financials; Value == heating_bill).

 

Note that charting a 8% increase over 10 years is pretty much pointless: the chart looks essentially the same no matter what the starting value is.

 

 

ProjectionChart.fp7

Edited July 1, 2015 by comment

[Rikki]

The Financials table is related to the customer database by the customer ID, it just stores many calculations that I use to generate a bunch of reports per customer. It only has 1 record per customer not multiple. The only reason I am not doing this from the Customers database is because it has 1.5 million records and is being accessed non-stop by the telesales guys, so if I add hundreds of new fields it takes ages to update all 1.5 million records with the modifications and during that time all of the telesales guys are locked out of the records for the duration and it cause a lot of problems. I decided I would just use a new table and relate it to the customer table to save hassle in that respect.

Also I do not need to generate these reports and charts for every customer in the database just certain ones. From the customers database I use a script to insert the customer ID into the foreign key field in the Financials database, so only the customers that I chose have a financials report.

 

I understand that it is "pointless" in your eyes as it doesn't "look" different but I have been asked by the boss to do it so that the customers can see a chart, instead of just the numbers in a table.

[comment]

The Financials table ...

It only has 1 record per customer not multiple.

Well, then you could produce the chart from the Financials table directly.

[Rikki]

My problem with that is like I stated originally, I don't know how I would make it produce a chart like my example. This is why I created a new table called CHARTS and added 10 records (one record per year) In my example chart each "year" is a record, I could add a new record and name the "YEAR" field "Year 11" and it would then show in the chart, then I would have to add a new "Case" in the calculation to show the value for year 11.

I just cannot get my head around how I would write a calculation for the horizontal axis to show the ten years.

[comment]

My problem with that is like I stated originally, I don't know how I would make it produce a chart like my example.

Have you looked at the file I have posted earlier?

[eos]

I just cannot get my head around how I would write a calculation for the horizontal axis to show the ten years.

You could do this without any additional fields (or tables, for that matter) by specifying the calculation

Let (
  heat = Financials::heating_bill ;
  List (
    heat ;
    1.08 * heat ;
    (1.08 ^ 2 ) * heat
    // ; and 7 more
  )
)

for the y-series, and

List ( "Year 1" ; "Year 2" ; "Year 3" /* ; etc. */ )

for the x-series, and selecting the “Current record (delimited data)” option as data source.