Jump to content

Sales report - TONS of calcs - help?!


This topic is 5677 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have a 33000-record table of merchandise sales and returns that I have been able to manage pretty fairly with FMP. However, we are moving into more and more complex reporting and I find that the only way to classify line items for proper aggregation and summarization is to pack dozens of calcs into each line item...basically logical tests that tell FMP if a line is a sale or return, what month the transaction occurred in, etc...

At the end of each month or quarter, I'd love to "freeze" the calc'd characteristics -- sort of the equivalent of Excel's "Paste Special -> Paste As Values"... Just looking for a way to avoid overloading FMP with tons of live/unstored calcs.

Any experience or ideas here?

Link to comment
Share on other sites

Sounds like you are using calculations with Aggregate functions to summarize your records. While they work, it is not the way FileMaker was designed for reporting. What you want to use is a subsummary report. Create a layout with as subsummary part sorted by the month. Your month formula might look like this:

Right("0" & Month(DateField); 2) & Year(DateField)

You need to add a leading zero to the month field (if needed) to make it sort correctly since the result of this formula needs to be text.

You can also create another calculation to grab the Month Name and year for display purposes.

MonthName(DateField) & ", " & Year(DateField)

Place this calculation field in the subsummary part. For simplicity purposes, you need any other parts but you might need a summary field which will give you the results like an aggregate function.

The benefit with a subsummary report is it obeys found sets, unlike relationships. So, you can find on any criteria and create a report on the records in the found set.

To view a subsummary report, sort your record by the sort field attached to the subsummary part and preview or print. This report will not show in browse mode.

This will create a much more flexible and responsive report.

Link to comment
Share on other sites

I find that the only way to classify line items for proper aggregation and summarization is to pack dozens of calcs into each line item...basically logical tests that tell FMP if a line is a sale or return, what month the transaction occurred in, etc...

Just looking for a way to avoid overloading FMP with tons of live/unstored calcs.

What are these "dozens of calc" and why can't they be stored?

Perhaps you just need to lookup the date (and maybe another detail or two) from the Invoice into the LineItem, so that calcs based on this data can be stored?

Link to comment
Share on other sites

Right("0" & Month(DateField); 2) & Year(DateField)

You need to add a leading zero to the month field (if needed) to make it sort correctly since the result of this formula needs to be text.

You can also create another calculation to grab the Month Name and year for display purposes.

MonthName(DateField) & ", " & Year(DateField)

I prefer using a date calc for this. A simple theDate - Day(theDate) + 1 produces a generic 'month date'. This is ALL you need here. It sorts perfectly. It can be displayed on reports in ANY way you wish (totally eliminating the need for your second CALC). It is a treat in reporting and can properly summarize by month-year. Heck, it can even be placed as merge field where you can change the date format as well to display May, 2006 or 2006 - May (or any other format that can be applied to date). Try that with a text-date.

In addition, I use this ONE calculation for conditional portals. These 'generic' date calcs have become an essential part of my designs and once you've tried them you will never go back. :wink2:

LaRetta

Link to comment
Share on other sites

Um, in addition John, that text date WON'T sort correctly. It will put all January together THEN the year. The year will need to be first to get a proper sort. Better to just use a REAL date calc here to begin with. :wink2:

Link to comment
Share on other sites

I like your formula. It could also be written:

Date(Month(theDate); 1; Year(theDate))

Like you said, this will sort by month properly. It also could be used for display purposes by use Date format to show just the month name. This could be done with the custom format option and removing the day, year and separator but leaving the month name.

Link to comment
Share on other sites

JM Osborne - Yep, I've gone the "aggregate across tables" route...why?...flexibility, mostly. I need to show a vendor with X items (might be one item, might be 10-12) their sales history for each item ( broken down by sale or return / who bought or returned / month the sale or return occurred / etc ), a little graphic representation of all those, and a grand total for the vendor. I have experimented with summary reports, but it seems that you lose some drill-down detail capability..?...

Mr./Ms. Comment - I can't store the calcs because I use a globally stored "quarter end" date as a measuring stick/filter. When I try to store results, it says I can't because the calc is using a related/global field.

I realize I may be off my rocker, by the way!

Link to comment
Share on other sites

I'm afraid you're not quite on your rocker :)-] Financial results need to be stored. You can calculate a "quarter end" just as you can year-month. And it can be for whatever you consider as your financial year. Here is an example. DatePlaced is the relevant Date field: I later combine this with the Financial Year; if your year is just the normal year, then it's simpler, just Year (DatePlaced):

_cFinancialYear (number) =

Case (

Month ( DatePlaced ) > 8; Year ( DatePlaced ) + 1;

Year ( DatePlaced )

)

_cFinancialQuarter (number) =

Case (

DatePlaced ≥ Date ( 9 ; 1 ; Year (DatePlaced) ) and DatePlaced < Date ( 12; 1 ; Year (DatePlaced) ); 1;

DatePlaced ≥ Date ( 12 ; 1 ; Year (DatePlaced) ) or DatePlaced < Date ( 3; 1 ; Year (DatePlaced) ); 2;

DatePlaced ≥ Date ( 3 ; 1 ; Year (DatePlaced) ) and DatePlaced < Date ( 6; 1 ; Year (DatePlaced) ); 3;

DatePlaced ≥ Date ( 6 ; 1 ; Year (DatePlaced) ) and DatePlaced < Date ( 9; 1 ; Year (DatePlaced) ); 4

)

_cFinancialYearQ =

_cFinancialYear & _cFinancialQuarter

// use &, not +

Also, if you need to break date out for specific reasons you can do this by creating stored calculations that do so. It is somewhat "redundant" data, but it's worth it (it's not a lot of data really). If, for example, your sales and returns were entered into the same Amount field, one as positive, one as negative:

_cSales = Case ( Amount > 0; Amount)

_cReturns = Case ( Amount < 0; Amount)

That way you can present each separately on a report. The above example may not relevant, but you get the idea.

One last thing. By introducing an unstored field to a calculation, you cause the calculation to be unstored. You also cause all calculations derived from it to be unstored, sort of a "cascading unstore". FileMaker will NOT turn them back to stored once you fix that bottom one. You have to do each manually.

Link to comment
Share on other sites

And said a bit differently ... By using REAL date calcs in your LineItems, you have indexable FLEXIBLE dates which can be used in non equi-join (greater than & less than) relationships to restrict portals (taking big bites out of the size of your LineItems) and making your aggregates (and portals) FAST. And format abounds. Examples:

Fiscal Quarter: Date ( 9 ; 1 ; Year ( theDate ) - ( Month ( theDate ) < 9 ))

Carry that through to other date groups ...

Calendar Quarter: Date ( Month ( theDate ) - Mod ( Month ( theDate ) - 1 ; 3 ) ; 1 ; Year ( theDate ))

Week: theDate - DayOfWeek ( theDate ) + 1

Year: Date ( 1 ; 1 ; Year ( theDate ) )

You will use these same date calcs over and over throughout your solution, changing their field-level display format as needed for various reports, attaching them for portals, searching, sorting properly and more. This is extremely powerful stuff which can save countless Developer hours and resources.

Oh, and John? That wasn't MY formula nor idea. This is stuff I learned from Comment. I've been studying dates from Comment and Ray since I discovered FM. :wink2:

LaRetta

Link to comment
Share on other sites

Fenton--

Your calc:

_cFinancialYear (number) =

Case (

Month ( DatePlaced ) > 8; Year ( DatePlaced ) + 1;

Year ( DatePlaced )

)

Could more simply be:

_cFinancialYear (number) =

Year ( DatePlaced ) + (Month ( DatePlaced ) >= fyStart)

(As you noted before, if a calendar year is being used, this would be superfluous—and wrong)

And I think _cFinancialQuarter could be:

Ceiling((Month( DatePlaced )) / 3 ) – (int(fyStart/3) - if(month(DatePlaced) < fyStart; 4; 0))

This assumes a global fyStart field that sets the month in which your fiscal year starts.

They are small points, but I think the calculations are clearer and more efficient.

David

Link to comment
Share on other sites

  • 2 weeks later...

Thank you all for some really excellent input!

My Transactions/LineItems table has NO unstored calcs and the performance has really improved. I am finding straight portal-based filtering back to the item and vendor a little restricting, though (my sales report needs to display numbers that apply to the current quarter -- for which calcs work great with a simple relationship filter on the quarter end date -- but I also need to show non-applying numbers such as "unrealized sales in the pipeline" and "returns that have already hit your account")... FYI, my relationship graph approach is Vendor->Item->Transaction/LineItem... (basic graphic attached)

Because of the necessary table-occurrence "context" restrictions of the aggregation calcs, however, I'm finding it impractical to work in the non-current/non-applying numbers. I'm hesitant to create a clone version of each aggregation calc for each occurence's context -- am I just being lazy, or missing something straightforward?

Picture_1.png

Link to comment
Share on other sites

This topic is 5677 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.