Charting Monthly Report

[Matthew R White]

I have 2 fields I'm looking to chart, one field is the policy effective date field ex: 1/29/2011, the other field is the total charges for each policy. I've got the effective date on the x-axis and the total charges on the y axis. The problem is, I'm trying to limit the data to each month, example 01/01/2011-01/31/2011, but as it currently stands its showing the data for the entire year, all records. Any ideas?

Thanks

Matt

[comment]

I am not sure I understand your question. If your chart is set to use data from current found set, and you want to chart data from a specified month only, start by performing a find for data in the specified month.

[Matthew R White]

I understand that, but how do I show that on the chart for each month, Jan Feb, etc, I can only find one month at a time.

[comment]

Do you mean you want your x-axis to be months?

[Matthew R White]

Yep

[comment]

Well, then you cannot do this:

I've got the effective date on the x-axis

See if this can get you started:

ChartByMonth.zip

[Matthew R White]

Many thanks!

[nleon307]

Is there a simple way to do this for related records? I currently have a crazy workaround but would like to just be able to use a Summary field from the related table. I would like to do this with related records so I can have it as a tab that can viewed record by record instead of going to the related records and viewing the chart. Same scenario with multiple records per month that I want to group together and chart.

Thanks

Nina

[comment]

Is there a simple way to do this for related records?

There is a way, but not a simple one. This is not really about charting; there is no simple way to show sub-summaries of child records in the parent table in general.

[nleon307]

I do have a workaround, but I'd love to simplify it. So if I have to skip the subsummary part, so be it. (sigh - I so wish I could chart the subsummary - )

I currently have a self join in the child table and a Sum function to get the total by month by item.

I then run a script to flag only one record per month per item and pull in the sales by month by item into the chart in the parent.

Can this last step of flagging the records be replaced?

[comment]

sales by month by item

Not sure where "item" comes into this - is that the parent record?

[nleon307]

yes

[comment]

Have a look at the attached:

ChartUgo.zip

Note that one of the extra TOs could be removed - see:

http://fmforums.com/...post__p__340208

But then you would need a custom function to produce the chart.

[nleon307]

I'm not seeing anything in the zip file for some reason.

I saw the post on deduping a portal which led me to believe there was a better way to do the chart. I'm just having a problem figuring out how to put it all together.

[comment]

I'm not seeing anything in the zip file for some reason.

I have downloaded it just now and it seems fine.

[nleon307]

I'll try another computer.

Thanks a lot

Nina

[nleon307]

I finally got it working... although not easily.

I think that is exactly what I was looking for. I think I can use this type of calculation/relationship combo in several places. Thank you so much.

[LaRetta]

 

Can you help with creating a demo for this type of chart? (or am I being too lazy?)

 

 

Well I gotta love ya for honesty.  

I would love to assist but I'm currently on iPad and I don't know charts as well as Comment anyway.  And I wanted to welcome you to FMForums!!  

[comment]

I have multi-year data - going back about 10 years. What would be great is to have a modification to the chart to show month by month comparison per year. To compare how things went for each Dec for each year, etc.

 

Not sure how to read this. Do you mean 12 charts (one per month), each showing 10 data points (one per year)? Or one big mess of a chart showing 120 data points at once?

[Harry]

I'm totally pulling my hair out.

 

Reporting skills are not something that are keeping up with my other FM skills at all.

 

I have a data set. Each record has a product code and a Time and a Timestamp that the record was created on. I currently run a report that groups by product code and then counts how many of each product code there is - displaying that quantity on the report.

 

So, I'm searching for the date that i want to enquire about first, then running the report.

 

What I'd like to do now is put the 'Time' field onto the report and Count all of those, too. But it just keeps coming up with wildly exaggerated numbers that i can't see how they've been generated.

 

Each product has a 'Time' associated with it. I want to plot a graph that shows the total 'Time' for all products on that day. I could search for a month or a quarter of the year and have a line graph that shows the amount of 'Time' for all the products on each day.

I can't understand what i'm not undertstanding about grouping, summarising or r whatever it is that I'm missing. I have three books open and I'm searching through fmforums.com.

 

I've downloaded the nice example you've built, Comment, the ChartByMonth.fp7 and it's great but i don't understand why it works and mine doesn't.

 

Please help me!

[Harry]

OK, I've serached for all records from January.

 

I have 3000 records.

 

I have a 'Day' field calculated and the report is grouped by that field, so each day is split into it's own set.

 

This leaves me with data that looks like this (Where the first column is ::StockCode and the second is ::Time):

 

6

12345     12.23

12345     12.23

12345     12.23

12345     12.23

12345     12.23

12345     12.23

98765     42.89

98765     42.89

7

98765     42.89

98765     42.89

98765     42.89

54321     09.12

54321     09.12

54321     09.12

54321     09.12

 

And so on for all the other days in that month where records have been created.

 

What I want to do is add up all the 'Time' from each day and end up with a report that was like this:

 

9

674.23

10

1287.40

11

321.87

 

etc etc, so all the days have a total of the Time that was created by those products.

 

I just cannot do it at all!

 

I have Summary fields of the Time field that are in the Summary Parts, but it just always shows the last number on that Group (09.12 on the example above), not the Total.

 

7

98765     42.89

98765     42.89

98765     42.89

54321     09.12

54321     09.12

54321     09.12

54321     09.12

09.12

 

It's driving me insane!

 

 

 

[Harry]

Argh! Surely a 'Total of ::Time' Summary Field should work??

 

I have another report that does a Count of these fields, so returns:

 

6 (th of the month)

8 (records)

7

7 (records)

 

etc etc.

 

I thought i'd be able to just put a Summary field of the Total of Time next to these fields and it would just add up all the values in that field, in that group. I don't see what i'm missing in my understanding here!

[comment]

If I am following you correctly (and that's a very big IF!) you want to arrange your report like this:

 

---------------------------------------------------------------------------

Header

---------------------------------------------------------------------------

Sub-summary part when sorted by cDatefield:

<<cDatefield>><<sTotalTime>>

---------------------------------------------------------------------------

Footer

---------------------------------------------------------------------------

 

where cDatefield is calculated from the Timestamp field (I think) and sTotalTime is a summary field (Total of Time).

 

This is assuming you want to show the total time of each date, regardless of product code (although you show records sorted by product code, which is not necessary for this type of report).

[Harry]

Yes, you're correct.

 

So in my example, i'd have

 

6

159.16

 

7

208.04

 

(Please excuse my indents - tab is not working in this text edit box.)

 

I am lost.

OK, I have one Report Part that is a Subsummary of ::Day When sorted by day

 

The ::Day field is in it.

 

I also have a Summary field (::Total) next to it.

 

The Day bit works, the Summary doesn't.

 

I can't get it to add up all the numbers in that field on those days.

 

This is so simple, isn't it?

 

Oh and no, it doesn't matter about the product codes at all. They are just sorted like this so i could easily see where i'm going wrong on a larger data set.

 

I want to graph this - put month by month data up on a line graph to show 'Total Time' as the line on the Y and Date across the bottom on the X. I'd love to run something similar for a year or last year etc etc etc.

Edited February 14, 2014 by Harry

[comment]

Why are there :: characters before your field names? Aren't you producing the report from a layout of the table where these fields reside?

[Harry]

Date is in the table, yes. TotalTime is a Summary field from a different table. Sorry, i am using :: to show it's a field name.

[comment]

TotalTime is a Summary field from a different table.

 

That makes no sense. Earlier you said:

 

I have a data set. Each record has a product code and a Time and a Timestamp that the record was created on.

 

That's one table, and the summary field to summarize the total time needs to be in this table, too. Unless I am totally misunderstanding what you're doing - in which case you need to start from the beginning.

[Harry]

Right, OK.

 

The table I'm working on - Inspection - has the time stamps and also a Works Order field. The Works order field is linked to a Works Order table that has the products Stock Code. The StockCode is linked to the Products table that has the 'Time' that it takes to make that product.

 

So, it's a relationship through those.

I search for all records in Inspection that occur in my date range that i want to see.

The Summary field is in the Products table. Can i put a summary field into the Inspection table that summaries a related field in a different table?

No it seems i can't do that.

 

Are you saying my summary field must be in the Inspection table?

 

Should i base the report on the Products table to allow me to prioritise the Summary field there, and then relate back to the Inspection table?

[Harry]

Would a calculation field work? Should I count them, then multiply the Time by the Count?

I'm totally lost. I just want to multiply two numbers together!

[comment]

So I understand:

 

Products -< Work Orders -< Inspections

 

If you wanted to summarize Inspections by Product (i.e.Stock Code), you could count the inspections of each code, then multiply the result by the corresponding time value from Products. But if you want to summarize by date, regardless of the product, you must define either a calculation field  ( = Products::Time) or a lookup field to do the same thing in the Inspections table. Then use a summary field to summarize the calculation/lookup field for the purpose of this report.

[Harry]

I added a cField to pull that information over to the table, and then summarised it and it works. So thank you. 

 

 

But i guess i need to do a lot more learning on why i can't use the related fields to summarise by. There's a huge part of this i'm just not getting.

 

Thank you for sticking with me and bringing me through this problem. I will go read.

[adamworking]

Hi

How would you edit this to report by year, rather than month?

Thanks

Adam

[adamworking]

Also, how would you edit ChartByMonth.fp7 to show the count of how many transactions per month?

Thanks