Matthew R White Posted January 6, 2012 Share Posted January 6, 2012 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 Link to comment Share on other sites More sharing options...
comment Posted January 6, 2012 Share Posted January 6, 2012 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. Link to comment Share on other sites More sharing options...
Matthew R White Posted January 6, 2012 Author Share Posted January 6, 2012 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. Link to comment Share on other sites More sharing options...
comment Posted January 6, 2012 Share Posted January 6, 2012 Do you mean you want your x-axis to be months? Link to comment Share on other sites More sharing options...
Matthew R White Posted January 6, 2012 Author Share Posted January 6, 2012 Yep Link to comment Share on other sites More sharing options...
comment Posted January 6, 2012 Share Posted January 6, 2012 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 Link to comment Share on other sites More sharing options...
Matthew R White Posted January 9, 2012 Author Share Posted January 9, 2012 Many thanks! Link to comment Share on other sites More sharing options...
nleon307 Posted February 15, 2012 Share Posted February 15, 2012 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 Link to comment Share on other sites More sharing options...
comment Posted February 15, 2012 Share Posted February 15, 2012 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. Link to comment Share on other sites More sharing options...
nleon307 Posted February 16, 2012 Share Posted February 16, 2012 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? Link to comment Share on other sites More sharing options...
comment Posted February 16, 2012 Share Posted February 16, 2012 sales by month by item Not sure where "item" comes into this - is that the parent record? Link to comment Share on other sites More sharing options...
nleon307 Posted February 17, 2012 Share Posted February 17, 2012 yes Link to comment Share on other sites More sharing options...
comment Posted February 17, 2012 Share Posted February 17, 2012 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. Link to comment Share on other sites More sharing options...
nleon307 Posted February 18, 2012 Share Posted February 18, 2012 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. Link to comment Share on other sites More sharing options...
comment Posted February 18, 2012 Share Posted February 18, 2012 I'm not seeing anything in the zip file for some reason. I have downloaded it just now and it seems fine. Link to comment Share on other sites More sharing options...
nleon307 Posted February 18, 2012 Share Posted February 18, 2012 I'll try another computer. Thanks a lot Nina Link to comment Share on other sites More sharing options...
nleon307 Posted February 18, 2012 Share Posted February 18, 2012 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. Link to comment Share on other sites More sharing options...
LaRetta Posted November 12, 2013 Share Posted November 12, 2013 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!! Link to comment Share on other sites More sharing options...
comment Posted November 12, 2013 Share Posted November 12, 2013 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? Link to comment Share on other sites More sharing options...
Harry Posted February 14, 2014 Share Posted February 14, 2014 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! Link to comment Share on other sites More sharing options...
Harry Posted February 14, 2014 Share Posted February 14, 2014 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! Link to comment Share on other sites More sharing options...
Harry Posted February 14, 2014 Share Posted February 14, 2014 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! Link to comment Share on other sites More sharing options...
comment Posted February 14, 2014 Share Posted February 14, 2014 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). Link to comment Share on other sites More sharing options...
Harry Posted February 14, 2014 Share Posted February 14, 2014 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted February 14, 2014 Share Posted February 14, 2014 Why are there :: characters before your field names? Aren't you producing the report from a layout of the table where these fields reside? Link to comment Share on other sites More sharing options...
Harry Posted February 14, 2014 Share Posted February 14, 2014 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. Link to comment Share on other sites More sharing options...
comment Posted February 14, 2014 Share Posted February 14, 2014 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. Link to comment Share on other sites More sharing options...
Harry Posted February 14, 2014 Share Posted February 14, 2014 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? Link to comment Share on other sites More sharing options...
Harry Posted February 14, 2014 Share Posted February 14, 2014 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! Link to comment Share on other sites More sharing options...
comment Posted February 14, 2014 Share Posted February 14, 2014 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. Link to comment Share on other sites More sharing options...
Harry Posted February 26, 2014 Share Posted February 26, 2014 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. Link to comment Share on other sites More sharing options...
adamworking Posted September 22, 2014 Share Posted September 22, 2014 Hi How would you edit this to report by year, rather than month? Thanks Adam Link to comment Share on other sites More sharing options...
adamworking Posted September 22, 2014 Share Posted September 22, 2014 Also, how would you edit ChartByMonth.fp7 to show the count of how many transactions per month? Thanks Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3505 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 accountSign in
Already have an account? Sign in here.
Sign In Now