Difference between two summaries in a Crosstab

[tjw2006]

Hi, I've got a crosstab which is showing orders per week and summarizing by month, I want to be able to compare each month's summary to the previous month's summary to do some conditional to do some conditional highlight i.e. if one month's summary is greater than the previous month's, then it is highlighted in green.
What I can't figure out is how to reference a previous summary in a formula. The data is laid out as below:

Jan Feb
Week: 1 2 3 4 Month Total Week: 1 2 3 4 Month Total
Company 1 10 20 60 40 130 20 10 30 50 110
Company 2 40 25 55 30 150 25 25 50 30 130

Thanks.

 

[tjw2006]

Anybody?!

 

[lbass]

Are there a small number of companies? A consistent number?

-LB

 

[tjw2006]

The companies will be added as parameter values to the underlying stored procedure, so there won't be many - 10 at most I should think. The whole premise behind this is, can you reference the previous summary value for a summarized field and subsequently do a comparison in a crosstab?

Thanks.

 

[lbass]

This is a somewhat complex solution and uses formatting formula areas to carry forward variable values. Since crosstab cells are processed down then across, you have to specify the row within which you are making the comparison. This solution is for comparison of the month totals by row.

First, select the month totals->right click->format field->suppress->x+2 and enter (this example is based on six countries--repeat the sequence up to the maximum number of countries):

whileprintingrecords;
numbervar cnt := cnt + 1;
numbervar curr1;
numbervar prev1 := curr1;
numbervar curr2;
numbervar prev2 := curr2;
numbervar curr3;
numbervar prev3 := curr3;
numbervar curr4;
numbervar prev4 := curr4;
numbervar curr5;
numbervar prev5 := curr5;
numbervar curr6;
numbervar prev6 := curr6;
if cnt=1 then
curr1 := currentfieldvalue;
if cnt=2 then
curr2 := currentfieldvalue;
if cnt=3 then
curr3 := currentfieldvalue;
if cnt=4 then
curr4 := currentfieldvalue;
if cnt=5 then
curr5 := currentfieldvalue;
if cnt=6 then
curr6 := currentfieldvalue;
false //so that the rows are not actually suppressed

This solution also requires that you have column totals at the top or bottom of the crosstab. The contents of the column totals and the label can be suppressed and the height of the row minimized or even colored to look like a design feature.

Select the column total for the month totals->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar cnt := 0;
true

This resets the row count so that each company is identified with a row number 1 to 6, so that summaries specific to the row can be compared.

Next, select the monthly totals again->right click->format field->borders->color->background->x+2 and enter:

whileprintingrecords;
numbervar cnt;
numbervar curr1;
numbervar prev1;
numbervar curr2;
numbervar prev2;
numbervar curr3;
numbervar prev3;
numbervar curr4;
numbervar prev4;
numbervar curr5;
numbervar prev5;
numbervar curr6;
numbervar prev6;
if cnt=1 and
curr1 > prev1 then
crgreen else
if cnt=2 and
curr2 > prev2 then
crgreen else
if cnt=3 and
curr3 > prev3 then
crgreen else
if cnt=4 and
curr4 > prev4 then
crgreen else
if cnt=5 and
curr5 > prev5 then
crgreen else
if cnt = 6 and
curr6 > prev6 then
crgreen else
crnocolor;

Again, add to the sequence to correspond to the maximum number of companies.

The above formula would color the first months values green because the previous value = 0. If you don't want this, change the formula to:

whileprintingrecords;
numbervar cnt;
numbervar curr1;
numbervar prev1;
numbervar curr2;
numbervar prev2;
numbervar curr3;
numbervar prev3;
numbervar curr4;
numbervar prev4;
numbervar curr5;
numbervar prev5;
numbervar curr6;
numbervar prev6;
if cnt=1 and
prev1 <> 0 and
curr1 > prev1 then
crgreen else
if cnt=2 and
prev2 <> 0 and
curr2 > prev2 then
crgreen else
if cnt=3 and
prev3 <> 0 and
curr3 > prev3 then
crgreen else
if cnt=4 and
prev4 <> 0 and
curr4 > prev4 then
crgreen else
if cnt=5 and
prev5 <> 0 and
curr5 > prev5 then
crgreen else
if cnt = 6 and
prev6 <> 0 and
curr6 > prev6 then
crgreen else
crnocolor;

-LB

 

[tjw2006]

Thanks very much Lbass, I will give this a try - the only possible problem is that there won't be a static number of companies, but I will give this a go and hopefully it will do the job. I'm not in work again until Thurs, but will let you know how it goes.

Thanks again.

 

[lbass]

You just need to set this up for some number greater than the likely maximum. It doesn't matter if you allow for more companies than there actually are.

-LB

 

[tjw2006]

Ok, will do - I'll set it up for a 100 or something - that should do the job lol. Thanks again & I'll let u know how it goes.

 

[lbass]

You said 10 at most earlier. It doesn't matter if they aren't the same every time. So I would think 15 or so would probably be sufficient--but only you know.

-LB

 

[tjw2006]

Hi lbass, I tried your solution and it's currently not working - perhaps I'm doing something wrong, but I've added the code to the relevant sections as you suggested. When you mentioned adding the code to the column totals, did you mean the column grand totals? At the moment it looks as though the variables aren't being set to the correct values.

Thanks again for all your help with this.

 

[lbass]

The reset belongs in the monthly total cell that intersects with the column grand total.

-LB

 

[tjw2006]

Yes, this is what I have done, but there is no background color changes whatever the values are between each monthly summary value. Is there any way of checking what values a variable is being set to, like a way of debugging a variable?

Thanks.

 

[lbass]

First add the summary field again, so that you have the same value in each cell twice. You can check the cnt value, by adding a formula to one of them in format field->display string area:

numbervar cnt;
totext(cnt,0,"")

This should return the same number, e.g., 1 in the first row of each company.

You can also check individual variables by replacing that with the following, e.g., for curr6.
whileprintingrecords;
numbervar curr6;
totext(curr6,0,"");

If your crosstab is more complex than you originally showed, you should explain the exact setup.

-LB

 

[tjw2006]

Ok, thanks once again lbass - at least now I can see what's going on with the variables. I know what needs to be done now, but not sure how it's achievable. Basically, I need to compare the value of the variable, which is set to each current summary value, to the value of a variable set to the previous summary value, which works out to be 18 crosstab cells before each current value. Do you think there would be a way of doing this dynamically without having to explicitly compare the variable values i.e. if curr19 > curr1 then crgreen else crred etc.

Thanks for all your help with this.

 

[lbass]

But that is what my solution is supposed to do--compare the current summary value to the previous summary value (February compared to January, March compared to February, etc.)--and I did test this, so know it works. Are you now saying you want to skip certain summary values?

If I understand you correctly, I think you have implemented this incorrectly. Please explain what results you saw when you checked the values for cnt. Also, please paste in the exact formulas you used and also explain where you placed them.

-LB

 

[tjw2006]

Hi, perhaps I wasn't clear enough in my description of the problem, but basically I just wanted to compare each summary to the previous summary on the same row. As it happens, I've managed to almost solve the problem with the help of your code to show the counter variables value. Referring to the value 18 counts behind the current summary value is almost doing the job, the only problem now is that every now and again the counter is increasing by 2 instead of one, which is throwing out the calculation. I'm not sure why this is happening at the moment - would you have any ideas?

Thanks.

 

[lbass]

Again I think you have implemented this incorrectly. The solution is designed to compare the summaries in the same row. I think from what you have said, that you have placed some of these formulas in the weekly columns--which you should not have done. If you want help troubleshooting this, please respond as requested in my last post.

-LB

 

[tjw2006]

Ok, firstly the counter variable is incrementing by one for each row for each summary and then continuing on for the next column of summary values, although as I said, occasionally this is incrementing by 2 when it's continuing on to the next column of summary values. As for the formulas, I've placed the following in the suppress section of the summary field:

whileprintingrecords;
numbervar cnt := cnt + 1;
numbervar curr1;
numbervar curr2;
numbervar curr3;
numbervar curr4;
numbervar curr5;
numbervar curr6;
numbervar curr7;
numbervar curr8;
numbervar curr9;
numbervar curr10;
...etc.

if cnt=1 then
curr1 := currentfieldvalue;
if cnt=2 then
curr2 := currentfieldvalue;
if cnt=3 then
curr3 := currentfieldvalue;
if cnt=4 then
curr4 := currentfieldvalue;
if cnt=5 then
curr5 := currentfieldvalue;
if cnt=6 then
curr6 := currentfieldvalue;
if cnt=7 then
curr7 := currentfieldvalue;
if cnt=8 then
curr8 := currentfieldvalue;
if cnt=9 then
curr9 := currentfieldvalue;
if cnt=10 then
curr10 := currentfieldvalue;
...etc. with a false; at the end

In background color section of the summary field I've placed:

whileprintingrecords;
numbervar cnt;
numbervar curr1;
numbervar curr2;
numbervar curr3;
numbervar curr4;
numbervar curr5;
numbervar curr6;
numbervar curr7;
numbervar curr8;
numbervar curr9;
numbervar curr10;
...etc.

if cnt=19 and
curr19 <> 0 and
curr19 > curr1 then
crgreen else
if cnt=19 and
curr19 <> 0 and
curr19 < curr1 then
crred else
if cnt=20 and
curr20 <> 0 and
curr20 > curr2 then
crgreen else
if cnt=20 and
curr20 <> 0 and
curr20 < curr2 then
crred else
if cnt=21 and
curr21 <> 0 and
curr21 > curr3 then
crgreen else
if cnt=21 and
curr21 <> 0 and
curr21 < curr3 then
crred else
if cnt=22 and
curr22 <> 0 and
curr22 > curr4 then
crgreen else
if cnt=22 and
curr22 <> 0 and
curr22 < curr4 then
crred else
if cnt=23 and
curr23 <> 0 and
curr23 > curr5 then
crgreen else
if cnt=23 and
curr23 <> 0 and
curr23 < curr5 then
crred else
if cnt=24 and
curr24 <> 0 and
curr24 > curr6 then
crgreen else
if cnt=24 and
curr24 <> 0 and
curr24 < curr6 then
crred else
if cnt=25 and
curr25 <> 0 and
curr25 > curr7 then
crgreen else
if cnt=25 and
curr25 <> 0 and
curr25 < curr7 then
crred else
if cnt=26 and
curr26 <> 0 and
curr26 > curr8 then
crgreen else
if cnt=26 and
curr26 <> 0 and
curr26 < curr8 then
crred else
if cnt=27 and
curr27 <> 0 and
curr27 > curr9 then
crgreen else
if cnt=27 and
curr27 <> 0 and
curr27 < curr9 then
crred else
if cnt=28 and
curr28 <> 0 and
curr28 > curr10 then
crgreen else
if cnt=28 and
curr28 <> 0 and
curr28 < curr10 then
crred else
if cnt=29 and
curr29 <> 0 and
curr29 > curr11 then
crgreen else
if cnt=29 and
curr29 <> 0 and
curr29 < curr11 then
crred else
if cnt=30 and
curr30 <> 0 and
curr30 > curr12 then
crgreen else
if cnt=30 and
curr30 <> 0 and
curr30 < curr12 then
crred else
...etc.

...this last bit of code basically compares the current summary value to the summary value 18 counter cells away - which equates to the previous summary value on the same row.

If this is still not clear, I'll attach a screenshot to my next post.

Thanks.

 

[lbass]

You didn't even use my formulas and I don't see the reset formula either. The prev variables are saving the current variable for use in the comparison in the next variable. So please retry using my original formulas--exactly as shown.

The reset formula:

whileprintingrecords;
numbervar cnt := 0;
true

...goes in the red cell.

The other formulas (mine) should be added to the green cells.

Jan Feb
Week: 1 2 3 4 Month Total Week: 1 2 3 4 Month
Total 50 45 105 70 [red]280[/red] 45 35 80 80 [red]240[/red]
Company 1 10 20 60 40 [green]130[/green] 20 10 30 50 [green]110[/green]
Company 2 40 25 55 30 [green]150[/green] 25 25 50 30 [green]130[/green]

If you don't have a total row as shown above, add it back in by unchecking "suppress column totals". You can then suppress the row in preview mode, but you must add the reset to this row.

-LB

 

[tjw2006]

I did originally do what you suggested, but it didn't work, so I tried customizing the code to make it work. I did have the reset formula in there, just forgot to include it in my post. It seems to me that the formula you provided compared one summary to the summary in the row before it, however, what I need to achieve is a comparison between a summary and the summary for the month before which equates to the summary that is 18 rows before the that summary.