Date, Calendar, Group Count Help

[jmorrison]

Ok, I did some searching, but either my Search Foo is weak, or I just glossed right over this. What I want to do, if it's even possible, is take an existing table that has the field DateComplete, and count the number of records per month. I do this already for a report where you can input the starting day, and the ending day of the month and it pops up a counter that tells you how many reports were completed that month. What I want to do is create a piece of code that will start at a single beginning date, and then count the number of completed reports to the present day, and break it down by number of reports per month, without having to input each start and end date of every month. I didn't see any sort of function or add on that will do this, but if I am trying to reinvent the wheel as it were, please let me know. Is this even feasible, and if so, where would I go about looking to get the info? I don't mind trying to figure it out myself, just need a nudge in the right direction. Thanks!

Jason

[Tom Cone Jr]

Re: Date, Calendar, Group Count Help

Yes, this is feasible, using an xbasic script that fetches through the table and populates a series of variables, or writes results out to a temporary table. Might be a good one to work on if you want to learn a bit about xbasic scripting.

Tell us more about the range of dates. Would these be limited to a single calendar year?

Tell us more about how you want the computed results output. to a report? to the screen ? what?

[jmorrison]

Re: Date, Calendar, Group Count Help

Ideally, I would like to output the results to some sort of graph, or chart to show the number of reports in a given time fram broken down by months. I haven't started looking to see if Alpha is even capable of that, I wanted to get the sorting and counting part down first. The ranges could be from Day 1 up to the present day, or it could be a single calendar year, or anything in between. The key is to break it into number of reports, per month, regardless of the length of time. If I can't graph it, then a report would be fine, or maybe export to excel in a format that would allow me to graph it without too much work, whichever is ultimately more easy and less prone to problems. Thanks!

Jason

[Tom Cone Jr]

Re: Date, Calendar, Group Count Help

Ok.

Given the output you are looking for it seems best to store the counts in a separate table. This can be done without using an xbasic script. Here's an example using a summarize operation. Unzip the attachment to an empty folder.

Hopefully what you find will make sense.

If you want to change the field names in the result table you can't. But the solution is simple, just create a new table with the same fields, but assign different fieldnames. Then append the result table records to the new table and base your chart on the new table.

[Mike Wilson]

Re: Date, Calendar, Group Count Help

yes, a chart is very possible. It is not the easiest thing to do, although I have such graphs in a couple of apps. It requires a form based on the results table and containing a Simple chart activeX control. If I have some time, I'll try and work one up.

[jmorrison]

Re: Date, Calendar, Group Count Help

Tom - Thanks for the example, I will give it a look this afternoon.

Mike - That would be great if you have the time. Thanks! I've never tried to do it, so any pointers would be helpful.


Jason

[jmorrison]

Re: Date, Calendar, Group Count Help

Tom -

I tried that out, and that almost did exactly what I wanted. I copied the expressions over into my own summarize and it worked. All the counts were there, all the dates were there, but it was doing something odd, and I don't know if it's something I need to fix in my existing table, or just change an expression. It was listing the counts in groups. It would go, for example

2009 34 2009 January
2009 1 2009 February
2009 23 2009 January
2009 38 2009 February

etc etc until all the January February counts were complete, then all the way down to December, and then start the next year. Is there a way to filter it so that it adds them all together and THEN write them to the table? I even tried doing a sort on the existing table to put it in ascending order by completion date before running the summarize, and it still did the same thing. Thanks!

Jason

[Tom Cone Jr]

Re: Date, Calendar, Group Count Help

Jason, can't say. Would be happy to take a look, but would need your database, or at least the relevant tables and their dictionaries. Why not zip a copy and attach it to a reply here? If you do, please tell us the steps to follow to see the behavior you're describing.

[jmorrison]

Re: Date, Calendar, Group Count Help

Tom -

I will get a copy of it with some fake data in it as I can't give you the info that's actually in there(non-disclosure rules, you understand). It will take me a day or so to get it cleaned and I will post it up with exactly what I did. Thanks!

Jason

[jmorrison]

Re: Date, Calendar, Group Count Help

Tom -

Ok, after playing with it for a while, I figured out the grouping problem. All I did was tweak the line

cyear(date_done)+" " +cmonth(date_done) and changed the summarization field from First to Group By.

That solved the main issue I was having, however it revealed a second issue that is quite easy to replicate in your test program. I tried it there as well to be sure before posting. The dates go like this now (for example):

2008 October
2008 November
2008 December
2008 January
2008 February
2008 March
2008 April
2008 May
2008 June
2008 July
2008 August
2008 Septembe
2009 October
2009 November
2009 December
2009 January
etc etc etc...in the same pattern.

At first I couldn't see anything that would be causing it, but then I noticed that September was cut off before the final letter, as in my example above, and September is where all the breaks happen. I tried changing the length on the field in the table before running the count, but it always defaults back to 8 characters for the month. I tried running it without the year in front of it, and it still does it. In my limited xbasic experience, I don't know of a way to force that extra character length on the field as it runs the cmonth(date_done) function.

This is easily replicatable in your example by adding a single record at the end with a complete date in September.

If you have any ideas, please let me know. I am stumped on this one. I don't want to have to copy everything over, add the missing "r", and then resort everything to get it on the proper order if I can help it.

Thanks!

Jason

[Tom Cone Jr]

Re: Date, Calendar, Group Count Help

Jason, the summarize operation is a black box, the internal workings of which are concealed from us. I can't explain why the "r" is being dropped off "September". If you can live with three char abbreviations, here's an example that might help you. If you need it spelled out I think you'll have to hire someone to write the custom xbasic script for you, or spend the time necessary to develop the code yourself with a little coaching from this forum. Hope this helps.

Later on...
Updated the summary operation to force the grouping change from "first" to "group by".


-- tom

[jmorrison]

Re: Date, Calendar, Group Count Help

Tom -

Wow, I think I just figured it out! Apparently, when the Summarize function runs, and it converts the month it drops the leading 0 (i.e. 01 = January, 02 = February...etc etc etc) so when it sorts, it sorts
10
11
12
1
2
3
4
5
6
7
8
9

After playing around with it, on the summarize operation, change the first expression to

ltrim(str(year(datecomplete)))+PADL(ltrim(str(month(datecomplete))),2,"0")

and then everything is all right. I still don't know why it cuts off the R in September, the only thing I can think of is that for some reason Alpha defaults it to an 8 Character length, and since it's the only month with 9 or more, it gets the axe!.

I appreciate all the help! Now I just need to figure out if I can do a graph in Alpha, or if it will be easier to export to excel and just graph it straight off the data.

Cheers!

Jason