Getting sum of previous week 1

[Duckie66]

Hi,

I'm using CR9 with MySQL4.1.

Current grouping:
- Week 1
- Monday
- Tuesday
- Wednesday
- Week 2
- Monday
- Tuesday

,i.e based on week number and dayname.

I need to show the development percent from week 1's data in week 2 header ((week2 - week1)/week1*100). The problem is that I can't get the value of week 1 from anywhere. Sum({@data}, {@weekno}) gives me the sum of current week data, but how about the previous week? Sum({@data}) gives me the sum of all data.

If the only choice is to do comparing in report header, I can do it there. But still - how to get the value of specific week? I could show the values in cross-tab but it isn't enough.. I need to get the development percent.

Any ideas?

- Duckie

 

[lbass]

I think you could insert a running total {#week1}. Choose {table.amt}, sum, evaluate based on a formula:

{@weeknumber} = 1

Reset never.

This will carry forward the week 1 amt. Then change your development formula to:

(sum({table.amt},{@weeknumber}) - {#week1})/sum({table.amt},{@weeknumber}) *100

-LB

 

[Duckie66]

Thanks lbass, running total really carry forward the value.

I'd like to change that #week1 to #weekno-1... So the value in the running total would be always from the previous week - So week 8 would be compared to week 7 etc. Is that possible?


And you seem to handle these tricks so I have to ask something more

Is it possible to calculate a 'floating year' in this same report? So header for week 26 would show the sum of previous 52 weeks. I have no year-group so all the data from specific week is reachable from week group.
Currently I have an idea that it is not clever to show that value in weekly report. But in fact, I have no glue how to do that in any else report either...

-Duckie

 

[lbass]

Could you explain your report design a little more? It sounds like it contains multiple weeks, not just two weeks, and for each week you want to compare weekly data with that from the previous week? Then you also want to display a sum for the previous 52 weeks on a rolling basis, I guess.

-LB

 

[lbass]

If my assumptions were correct, then you could do the following to get the "development percent", a measure of growth from the previous week:

1-Insert another group (week) header section_b.
2-Create a formula {@prevwksum}:

whileprintingrecords;
numbervar prevwksum;
prevwksum := prevwksum + {table.amt};
prevwksum - {table.amt};

Place this formula in both the detail section and in the group (week) header_a.

3-Create a reset formula {@reset} and place it in group (week) header_b:

whileprintingrecords;
numbervar prevwksum := 0;

4-Create your development percent formula {@devpercent}:

if not onfirstrecord then
(sum({table.amt}, {table.date},"weekly&quot - {@prevwksum})/{@prevwksum} * 100

Place {@devpercent} in the group (week) header_a.

To get the rolling total for the last 52 weeks, you could adapt the formula developed by bdreed35 in thread767-704190:

numberVar array Accum;
numberVar x;
numberVar y := 0;

if OnFirstRecord then
ReDim Accum [1]
else
ReDim Preserve Accum [UBound(Accum)+1];

Accum [UBound(Accum)] := sum({table.amt}, {table.date},"weekly" //Replace the amount and group field with your fields.

If UBound(Accum) < 53 then
Sum(Accum)
Else
(
for x := (UBound(Accum)-51) to (UBound(Accum)) do
(
y := y + Accum [x];
);
y;
)

This formula is really bdreed35's solution, almost exactly. I think it will do what you want, and if it does, thanks are due to Brian, not me. Place this formula also in group (week) header_a.

-LB

 

[Duckie66]

lbass,

your assumptions were correct. I'm been busy for a while and therefore I couldn't answer.

Today I tried your formula and it worked just so nicely.

You wondered my report design - I have a week &amp; day groupings only. So there are several years in the database but I want to group them by week because I want to compare the data. So it is nice to show data from several years in an one chart.

According to my week grouping, I'd like to ask something.. again

Would it be possible to get the rolling total for last week despite the year? When looking the week 1 it would tell total from last year etc. So how could I get the data from previous year too?

-Duckie

 

[lbass]

If what you mean is that you want to show a previous week's value even on the first group (weeknumber), then I think you should include the last week of the previous year in your record select statement and then suppress the first group (the last week of the previous year) in your report, so that the first week that displays is week 1 of the new calendar year. You can suppress the first group by going to format-&gt;section-&gt;group header/details/group footer-&gt;suppress-&gt;x+2 and entering:

groupnumber = 1

If this is not what you mean, please provide more detail or some sample data.

-LB

 

[Duckie66]

Ok, I'm sorry my terrible English.. Let's try again

Those solutions that you gave earlier worked just fine. I just wondered how to include to running total the earlier years too. If looking on week 3 for example, it would show sum from previous 52 weeks (until week 4 / 2003). It would be always 52 weeks back from current data. When looking on week 45 it would be 52 weeks back from there (until week 46 / 2002). So it doesn't matter how many years there are in a database, there is always maximum of 52 weeks.

-Duckie

 

[lbass]

Still not totally clear on what you're looking for, but if you mean you would like a formula that captures an earlier year's data, then something like the following might work (apologies again to bdreed for the awkward adaptation of his formula):

whileprintingrecords;
numbervar array accumx;
numbervar xx;
numbervar yx := 0;

if onfirstrecord then
redim accumx[1] else
redim preserve accumx[ubound(accumx)+1];

accumx[ubound(accumx)] := sum({table.amt}, {table.date}, &quot;weekly&quot;

if ubound(accumx) &lt; 53 then yx := 0;

if ubound(accumx) in 53 to 104 then
for xx := 1 to ubound(accumx) - 52 do
(
yx := yx + accumx[xx]
);

if ubound(accumx) &gt; 104 then
for xx := (ubound(accumx)-103) to (ubound(accumx)-52) do
(
yx := yx + accumx[xx];
);
yx;

This should return a year's worth of data for the year ending one year before the week under consideration.

-LB

 

[Duckie66]

Ok.. It seems that my grouping is still unclear.

Week 1
- Monday
- Tuesday
- Wednesday
Week 2
Week 3
...
...
Week 52

&lt;-- that would be status if there were more than one year. So every year is shown in its own bar in a graph. There can't be more than 52 weeks (actually more than 53, for example in this year), I use formulas @CurrentYearData and @LastYearData to count different things.
I tried to tweak that formula but I couldn't get the value from last year. In other words, what I need for week 1 is following:

week 1 currentyeardata + week 52 prevyeardata + week 51 prevyeardata... ... + week 2 prevyeardata

Is there any way to do that?

-Duckie

 

[lbass]

I am still unclear. Do you want something like:

Week52 Week51 Week50 Week 49 .....Week1
Week1 Week52 Week51 Week50 .....Week2
Week2 Week1 Week52 Week51 .....Week3

...where each row would be used for a graph?

Or are you saying you want to sum the previous 52 weeks data for each week? If this is what you mean, my Feb 20 post above shows you how to do that. The last formula in that post sums the previous year on a rolling basis, so that for each week it would show the previous 52 week total.

And how does the year factor in? You say, &quot;So every year is shown in its own bar in a graph.&quot;

It might be more helpful if you provided a report layout and indicated exactly what you wanted your report to look like, and if you somehow specified dates of data to be included.

-LB