Cross-tab average column

[ebstarasia]

I have a cross-tab that displays the weekly amount of files completed by each user.

Total Week 1 Week 2 Week 3
Total 568 168 212 188
Alex 220 50 90 80
Zack 180 60 60 60
Sam 168 58 62 48

The summarized fields/numbers are a count of v_oihouse.BillCompleteBY which is just the users name.

I want to add a column next to the total with the average amount files completed by each user.

 

[MCuthill]

ebstarasia,

Using the Cross-Tab Expert (the Cross-Tab, tab), simply add a Summarized Field, and define the summary type accordingly. The default (by memory) is to have the field added below, to change this to horizontal; go to the Customized Style taba nd changed the "Summarized Fields:" radio to "horizontal".

The Cross Tab expert contains pretty much every option you can change on an object of this type, for future questions regarding the basics of Cross-Tabs, I would check there first.

Hope this helps, Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

If you are willing to have the row totals on the right (change it on the customize style tab), you could select the column total cells (the top row)->right click->format objects->suppress->x+2 and enter:

whileprintingrecords;
numbervar wks := wks + 1;
false

Then add a formula {@0} as your second summary field (sum of {@0} and in the customize style tab, check summarized fields->horizontal->show labels and edit the text to read "Average". Select the inner cells for his new summary->format field->suppress. Then resize to minimize the width.

Then select the row total column (both total row and row instances) for count->format field->suppress->x+2 and enter:
whileprintingrecords;
numbervar addcnt := currentfieldvalue;

Then select the new average column->right click->format field->display string->x+2 and enter:

whileprintingrecords;
numbervar addcnt;
numbervar wks;
totext(addcnt/wks,0);

-LB

 

[ebstarasia]

lbass,

what exactly is the {@0} formula? is it just a null/empty formula?

 

[lbass]

Sorry, forgot to define it:

whilereadingrecords;
0

-LB

 

[ebstarasia]

whileprintingrecords;
numbervar addcnt := currentfieldvalue;

should this be true or false?

 

[lbass]

Sorry again. False. You are just using the formula area to establish the value behind the scenes, but you do NOT want to suppress the actual field value, so "false."

-LB

 

[ebstarasia]

I am getting an error message stating that you cannot divide by 0.
numbervar wks = 0

 

[lbass]

Did you put in the very first formula in the top/Total row? I can't see a situation where wks would be 0.

-LB

 

[ebstarasia]

Total Week 1 Week 2 Week 3
Total 568 168 212 188
Alex 220 50 90 80
Zack 180 60 60 60
Sam 168 58 62 48

From your instructions(trying to get this clarified), Move the total column to the right....

Week 1 Week 2 Week 3 Total
Total 168 212 188 568
Alex 50 90 80 220
Zack 60 60 60 180
Sam 58 62 48 168

Then add the suppress formula to

Week 1 Week 2 Week 3 [Total] <--This field
Total 168 212 188 568
Alex 50 90 80 220
Zack 60 60 60 180
Sam 58 62 48 168

Add the {@0} formula as second summary field

Week 1 Week 2 Week 3 Total
Count Avg Count Avg Count Avg Count Avg
Total 168 0 212 0 188 0 568 0
Alex 50 0 90 0 80 0 220 0
Zack 60 0 60 0 60 0 180 0
Sam 58 0 62 0 48 0 168 0

Then add the suppress formula to the row total column and row instances

Week 1 Week 2 Week 3 Total
Count Avg Count Avg Count Avg Count Avg
Total [168 0 212 0 188 0 568 0]
Alex 50 0 90 0 80 0 220 0
Zack 60 0 60 0 60 0 180 0
Sam 58 0 62 0 48 0 168 0

or did you mean?
Week 1 Week 2 Week 3 Total
Count Avg Count Avg Count Avg Count Avg
Total [168] 0 [212] 0 [188] 0 [568] 0
Alex [50] 0 [90] 0 [80] 0 [220] 0
Zack [60] 0 [60] 0 [60] 0 [180] 0
Sam [58] 0 [62] 0 [48] 0 [168] 0

I assumed it was the total column and count column for each row.
Then I selected each average summary field and entered the formula for the display string.

whileprintingrecords;
numbervar addcnt;
numbervar wks;
totext(addcnt/wks, 0);

Afterwards, when I preview the report, it errors out saying division by 0. where did i go wrong?

 

[lbass]

Then add the suppress formula:

whileprintingrecords;
numbervar wks := wks + 1;
false

to the following:

Week 1 Week 2 Week 3 [Total]
Total [red]168 212 188[/red] <--This field 568
Alex 50 90 80 220
Zack 60 60 60 180
Sam 58 62 48 168


Add the following formula:

whileprintingrecords;
numbervar addcnt := currentfieldvalue;
false

...to the suppression area for this column:

Week 1 Week 2 Week 3 [Total]
Total 168 212 188 [red]568[/red]
Alex 50 90 80 [red]220[/red]
Zack 60 60 60 [red]180[/red]
Sam 58 62 48 [red]168[/red]

You should be suppressing the inner cells of the crosstab that show 0, so that only the total column shows the {@0} summary. You would then minimize the width of the suppressed column.

Then right click on that 0 column summary (now next to the ones I identified in red above) ->display string and enter:

whileprintingrecords;
numbervar addcnt;
numbervar wks;
totext(addcnt/wks,0);

The results should look like:

Week 1 Week 2 Week 3 Total Average
Total 168 212 188 568 189
Alex 50 90 80 220 73
Zack 60 60 60 180 60
Sam 58 62 48 168 56

-LB

 

[ebstarasia]

thanks i got it to work now.

 

[ebstarasia]

Thanks for the help with this question. Sadly, I have to come back for this just for a quick help. I am using the report to do the weekly average, as mentioned above, and it works great. However, now I am asked to also have a report for the daily average.

I noticed that when i change the columns from weekly to daily that not all the days are shown, such as days that do not have any uploads at all (weekends for example). Therefore, the average result is only based on days that had activity.

Issue is how do I include only the weekdays (M-F) and exclude the weekend, as well as have days with no upload activity displayed?