line chart for weekly

[David Nelson]

I want to create a line chart which groups weekly information but I cannot figure it because weeks are strange. I have attached file. It shows how Week of year and week of year fiscal are different even when I use day 1 for fiscal start. And what I consider 'group by week' to produce different results does not match either of them. If I adjust so it matches (see file for year 2011) then it will not match for 2012. If I make it match for 2012 then it does not match in 2011. I do not understand. I have searched three forums and I am really tired.

I read that WeekOfYear should not be used and looking at file at 1/1/2011 shows why - only one day is in it. So I am trying to create a logical week of year number to match data spanning real weeks Sun - Sat. I have found a few calcs which have helped me get the starting Sunday date. But how do I total this group for the chart and how do I label the axis? I tried using a WeekOfYear start calculation which shows the WeekOfYear for the calculation which groups correctly as Sun-Sat but as you will see, it too is not consistent and starts with week 2 in 2011.

How do others handle week issues like this and how do I group records to give me a line chart of the totals? The chart I created does not work at all. Even the horizontal labels went weird in it. Can someone help me?

dbTest.zip

[comment]

how do I total this group for the chart

Use a summary field defined as Total of [Amount].

and how do I label the axis?

How do you want to label it?

[David Nelson]

I used a calculation Sum() calculation instead of a summary field and that was mistake. So I add summary field called Total Amount which is summary of Amount for my y axis. And I have WeekStart which collects them by their Sunday date for x axis. Sort report by WeekStart which is Sunday date. I get results!

The x axis turned all black because I had 104 or more weeks. That is second mistake. I must ensure the person does not try to find more records than can fit on the report so the labels fit. I might need table to hold layout name and number of acceptable columns? For your question, Comment, viewing x axis label, I think 1/2/11, 1/9/11 the Sunday date WeekStart makes most sense but I am open to all ideas you give.

So I have two reasons for wanting to create week numbers even after I found this WeekStart calc file - when script is run, first reason is count the weeks in the date range entered by person for find and make sure that the number of columns will fit on the layout, probably omitting records outside of a max weeks range value stored somewhere and also check the person's date range request and go backwards to the week's prior Sunday if the start day they enter is not Sunday (I guess this would need to be calculated before the prior reason). It is important that full weeks always be displayed for these comparisons.

Other than that then, I am close to what I need. Week numbers seems easier for me to move forward and back through than actual dates. Your assistance is greatly appreciated, Comment.

[comment]

I don't see a reason for numbering the weeks - unless that is what you want to use for your labels. In any case (as you have already discovered) week numbers are somewhat problematic at year boundaries. You also cannot use week numbers alone, unless you limit the found set to a specific year.

You do need to script the find in order to make sure the date range contains only full weeks (and limit the number of weeks shown, if you so prefer).

Note also that x-axis labels can be rotated.

[David Nelson]

Got it! Thank you.

I rotated the labels and I can get six months (roughly) on the chart in landscape. That is even better than I had hoped since rotating the labels. I cannot figure how to make position dots or a grid to better display the exact point of each x axis date. Is there any way?

I need to now figure how to handle the find. I am stuck on it. Should I post question on it here or go to the finding and searching section? This find does not specifically have to do with charts. I found several scripts for finding but none which do what I need or give me a hint on how to adjust their entry.

[comment]

I cannot figure how to make position dots or a grid

I am afraid that's not possible. Changing the chart style from [Curve Fitting] to [Poly-line] might help a bit, though.

Re the find: how do you want your users to specify the date range, before you correct it for them?

[David Nelson]

I sorta have script that almost kinda works (see adjusted file please, Comment, because it will probably be easier to see the size of the chart and number of columns I would like). But you do not need to use my file or any file. Anything you give will be great.

I have been thinking this through and I think this lists the considerations for the find

1) If they enter 1/3/11 in EnterStart then it should change to the Sunday prior (1/2/11). If it is Sunday that it should remain unchanged.

2) It should display a maximum of 28 weeks. I thought that way, if range needs to expand either direction, we could still get 6 months if needed.

3) From that possibly adjusted EnterStart in #1, go forward a maximum of 28 weeks. Here is where it gets very tricky

4) If the end date they enter is greater than the 28th week, go backwards to the prior Saturday. If less than the 28 weeks, go ahead and go foward to the next Saturday.

Since I will list the global dates on the chart as part of the header, it is okay if I change the dates they enter as long as it is in reason and includes the right records. I also forgot to check that this will work for leap year. I think it will since they are date fields but I do not need surprises.

I realize that this need to limit data by chunks might be needed for any chart which groups the amounts or whatever is being grouped and maybe not just month or year. And even for counts. I do not understand mathematics but these points on the chart represent a group of information and, if it is wrong (not all the group within it) then we will not know.

I need to understand how to move forward and backward through groups based upon the sort field. I will get to work on understanding it also.

dbTest2.zip

Edited November 26, 2011 by David Nelson

[comment]

It's not as difficult as you make it seem. First, calculate the starting Sunday as =

gStartDate - DayOfWeek ( gStartDate ) + 1

Next, calculate the maximum value for the ending Saturday as =

startingSunday + 195

Third, adjust the entered end date to Saturday =

gEndDate - DayOfWeek ( gEndDate ) + 7

Finally, take the lesser of the last two values as the end date for the find.

[David Nelson]

It works perfectly. I got lost trying to script if/else so I tried to do it all in one set Field. I hope that was okay - data shows right number of records found for proper range. Here is the calc for gEndDate in case I miss your meaning

Let([

maxEnd = dbtest::gStartDate + 195 ; // calculate the maximum value for the ending Saturday

adjEnd = dbtest::gEndDate - DayOfWeek ( dbtest::gEndDate ) + 7 ; // adjust the entered end date to Saturday

result = Min(maxEnd;adjEnd) // take the lesser of the last two values as the end date for the find

];

result

)

Also I notice my calc to get the group date is date - Mod ( date ; 7 ) and you gave me date - DayOfWeek ( date ) + 1. I added test in my records and they all produce same result so I think they always will, although I did not test leap year reasoning that we were not stopping at month boundaries so it was not a concern. Well, I can understand your calc although it took a bit of thinking and I never did figure out the Mod() one even reading up on it in help and multiple posts.

You made it seem so easy to walk it through. You are very good at this stuff. I have been picking at your posts like hungry vulture for few months now. :waytogo:

[comment]

my calc to get the group date is date - Mod ( date ; 7 ) and you gave me date - DayOfWeek ( date ) + 1.

Actually, I started with the Mod() version and changed it at the last moment, thinking it would be easier to understand. In any case, it's the same calculation.

I have been picking at your posts like hungry vulture

Watch out for indigestion... :tongue: