Percent question

[topjobs]

I can't figure this one out. I have an invoicing system where I want to be able to show, if possible on the same page, a report that shows total sales for last year, divided by months, this year's sales, divided by months, and the percentage difference between these 2 years and the percentage difference between each months. In case my English is not good enough, need a summary field showing on the first column last year and the total sales for last year. Under that, I want to see each month of the year with total sales for each the months. Next columns is the same, except it will represent the current year. Next to the year field I need to see the total sale for that year, and the percentage difference between last year and this year. Next to this year's months, I would like to see the total sales for each month, and a field with percent value, showing the difference between this year and last.

Thank you very much for your help.

[BuddySystems]

Gosh you're not asking too much...LOL!

What you're describing is to me at least, a little more than would normally be asked in a forum question or even multiple questions, but let me answer at least a little part of it....

Ok first, you're gonna need 24 calculated fields - the first 12 with the current year and each month and the client code for the current record, the second 12 with current year and each month and the client code for the current record. An example would be 1-2003-smith, and 1-2002-smith. Then you need to build relationships for each of these field. Then you need two calculated fields that are month of the current invoice year of the current invoice and client code of the current invoice, and month of the current invoice last year of the current invoice and client code of the current invoice. Then you need relationshipes between each of the fields mentioned above (24 in all) and calculations that adds last year and this year together, then you need a field that divides each months sales by that total, and that will give you your average.

Whew...that was a lot of explaining.....it would take me about three hours to develop that, so go to it!

[Ugo DI LUCA]

Right you are Shimoda !!

This would take some time to set up. Talking about "time", I know a user on this Forum that would "scratch" his hair when seeing so much related calculations taking place.

Knowing that the datas surely comes from a line item, according to the number of invoices you're dealing with, you'd likely get some speed problems here, due to cross records calculations.

You'd either better use all this bunch of related calculations into scripts, or/and store the statistical datas in some other fields, somewhere else (those from last year for example).

But Shimoda's accuracy is there waiting for you to implement it

[BuddySystems]

Hmmm...Frankly I haven't done this before...but it sounds like it might be a cool idea....

This idea provides some interesting challenges then - what relationships would be required with the scripting solution? I think to do what he wants to, the same relationships would be necessary, but the calcs would be done in the script.

Also I think it would work fine for a single customer layout, but wouldn't help for reporting purposes, though I don't know if additional reporting would utilize those relationships anyways.

The question then is how the performance of the script would compare to the defined calcs, and if there would be similar potential for display lag.

Ugo - do you have any examples of doing this in a complex situation like requested?

[Ugo DI LUCA]

Buddy or Shimoda ...

the same relationships would be necessary, but the calcs would be done in the script.

As a matter of fact, while developping a db, I always start a draft with all the calcs, fields and relationships you described.

When all is working fine, I replace, one by one all calcs fields by Update and Validation scripts using a SetField(field,::Yourrelated calculation).

Sure this would require some time to put together, but would considerably increase the speed of the entire solution.

There are lots of situation where "breaking relationships" would be used. An Inventory is probably the most common example of this process.

Statistics Reports could use the same process. You could drop the statistics data into a Statistics File, then involve a relationship to that file in the SetField scripts.

This has one Main advantage IMO. Whatever evolvements (and there surely would be...) you'll made to your file structure, field definitions, or even your business itself, you'd still be able to easily retrieve yearly, monthly or whatever statistics.

In situation where the statistic report would be based upon one single record (a Product, a Customer, a Vendor,...), you could use some statistic repeats fields in the given File (i.e. Product.fp5), updated by script anytime a Product is sold . Ready for calc with a repeat index field, this would be even faster as Sum(), Average() and other statstics fucntions are faster with repeats than with relationships.

There are chances that in the case we discussed, I'd use both the repeats and the related Statistic File. One for a Statistic Report by Customer, the other for the Incoice Reporting.