Help With Showing And Calculating Data

[Goetch]

Hi all, again I am so sorry if the answer to this is right in-front of my face. I have a simple sales database, each record has date, year, item number, quantity, price, cost, dept and category and maybe a few other fields. I have several reports that work well on this data base but there is one report that I would really like to make. I would like to be able to do is summarize any day and compare that number to the same day the previous year. I currently have reports and summarys that work for any given time period but I can not figure out how to show another summary for last year on the same screen or do calculations based on the summarized day, like the difference in units and margin.... Is this even something file maker can do? Any Ideas or clues to direct me in the correct direction would be greatly appreciated.........

Currently I run separate reports ( one for the current year and one for last year) in the same date range and plug in the summarized data in to excel to show the differences for our sales meetings, I have to think there is a better way to do this........

[comment]

See if this helps:

http://fmforums.com/forum/topic/56877-compare-dates/

[Goetch]

See if this helps:

http://fmforums.com/forum/topic/56877-compare-dates/

That solution is close, but I want to summarize certain data and then compare that in the same way..... My database is different, I have many entries for one day, so I would like to summarize my data and then compare, an example would be this:

Each record in my database is 1 line on any given invoice, so the data collected is the date, department, quantity, item, cost, retail, and I calculate the extended costs and retails and calculate my margins. I would like to be able to summarize all sales for any given day by department and then compare that to the same summary for last year. Is that possible to do via a portal? Forgive me of my ignorance if this is simple I just can not figure it out, and none of the file maker training I have taken has shown me a way of doing this.......

[comment]

I believe it is possible - but I am not sure what you mean by "all sales for any given day by department". How many days will there be in this report?

[Goetch]

I believe it is possible - but I am not sure what you mean by "all sales for any given day by department". How many days will there be in this report?

What my database contains sales records for about 3 years, each record is a line item of a sale. So any given day may have 1000's of records.... I make reports based on sub-summaries of this data based on the fields date and department usually. So for me to get 1 days totals it may be 100 to 1000 records of data. basically my reports right now take a date range that the user inputs and then summarizes those dates and outputs the summary based on department. (Department is a field on my records) I want to be able to take those same reports and compare to last year or the same day last year, heck I would even just take to comparing by the same date as last year just need some sort of comparison. I don't know if I answered your question or not, but basically I want to be able to compare a single day or date with the same day of year last year, as well as to compare a date range, like maybe a month to a month last year but I want the data summarized by department. I would also like to be able to compare maybe month to month like this month as to last month or maybe a 3 month range to the previous 3 month range...... I hope I made this a little more clear, I guess I could make small version of our current database and upload it if need be.

I saw some talk of a horizontal portal trick and some solution to show summarized data in fm5 so I am going to see if I can make either of those apply to my database.

[comment]

basically I want to be able to compare a single day or date with the same day of year last year

This could be done very simply.

CompareAggregates.zip

[Goetch]

This could be done very simply.

The single day was easy and I think I got that to work well, and I think I can integrate it for my needs, is there a way to do the same thing for a date range?

By the way thank you for that ah-ha moment.....

[bruceR]

CompareAggregates.fp7.zip

And here's a variation of Comment's report using a collector script with output to global field or merge variable.

[Goetch]

CompareAggregates.fp7.zip

And here's a variation of Comment's report using a collector script with output to global field or merge variable.

That is great, thank you for that input, I am learning that I am not nearly as smart as I think I am .... I added a field "Department" using this model is there a way to break the data down not only by date but also department totals? The current model is great for an overall view but I need to somehow translate that to department data, and if possible show a date range.....CompareAggregates 2.fp7.zip

I can not stress how much I appreciate everyone's input and help.

My predecessor left me in a bind, and didn't think about the future. They made a separate table to do what I want to do. They made this table with 6 entries per date (month day ) 1 entry for each department we have so a total of 3014 records in this table. Mind you this is all redundant data that I can pull from summaries in the sales table. Anyway they then made a field for department, then this will make you laugh, they made fields for each years numbers, so there was a field for 08 quantity, 08 retail o8 cost , then 09 retail, 09 cost, 10 retail, 10 cost, and more fields to show the differences from one year to the previous year.... So basically what they told me I had to do to make the database work for 2011 is make 2011 fields and a new layout to show those fields in the correct order..... I guess it does what the sales managers want but the general manager and the owner always want 3 month comparisons and other comparisons that this was not designed for. And to me it makes no sense double entering data, all the data is already collected via the sales table anyway, and to have someone go and enter summarized data makes no sense to me at least, but I can;t figure a better way to do it, but with the help I have gotten so far I know it can be done......CompareAggregates 2.fp7.zip

[comment]

is there a way to do the same thing for a date range?

Same thing, basically.

CompareRangesAggregates.zip

[comment]

The current model is great for an overall view but I need to somehow translate that to department data

The same question as before: how much do you want to show at the same time? If it's one department only, then the two relationships can be filtered by the selected department.

If it's all/some departments, then it would be better to place the globals in the Departments table and do the relationships from there.

[Goetch]

The same question as before: how much do you want to show at the same time? If it's one department only, then the two relationships can be filtered by the selected department.

If it's all/some departments, then it would be better to place the globals in the Departments table and do the relationships from there.

I want to show all departments at once, so I should make a departments table.

Again Thank you really thank you so much...

[comment]

I want to show all departments at once

Well, then:

CompareRangesAggregates+.zip

[Goetch]

Well, then:

Wow,where was my brain, I can't believe how simple that really was... Again thank you so so much, I tested this out on our current database and by just adding the new table departments everything pretty much works the way I wanted it to.. I can apply this to a few other databases I have, again I really appreciate the time and effort you put in.

thank you!

[Goetch]

Well, then:

One more question, I can not seem to figure out how to get an overall total for the date range..... Meaning the total sales for all departments as well for that date range.....

[comment]

For this, you will have to give up the convenience of using related summary fields, and replace them with aggregate calculations.

CompareRangesAggregates++.zip