Averages in a Cross Tab

[Dross]

I have seen similar questions here, but the answers don't seem to fit my scenario. I am sure it is something relatively easy that I am missing.

Using CR2016

I have a cross tab with the days of the week Sunday - Saturday. I have a distinct count of customer IDs for every day and it looks like the example below. The counts work good. They can select parameters for a whole week, month or a time period and they all seem to calculate correctly. I created a formula called CustCount and it is DistinctCount (Customer_ID)and put it in the cross tab under Summarized Fields and set the summary to Average of that field, but all it shows is the Grand total under every day (see below). I tried it several ways, but I never get it close to the example I have for expected results. I am looking to calculate the average customer count by the day of the week and do it in a cross tab. Any thoughts? I attached an image just in case the chart below wasn't clear.

SUNDAY--MONDAY--TUESDAY--WEDNESDAY--THURSDAY--FRIDAY--SATURDAY--TOTAL
831 992 746 784 719 702 876 5650 <--- CORRECT VALUES AS EXPECTED
5650 5650 5650 5650 5650 5650 5650 5650 <--- UNEXPECTED RESULTS

208 248 186 196 179 175 219 <---- EXAMPLE OF EXPECTED RESULTS

 

[lbass]

You can make use of formatting formula areas to do the calculations, like this:

Create a formula {@0} and add it as a summary field:

whilereadingrecords;
0

This creates a holder for your average in the total line. Suppress the summary that appears in the week rows and then minimize the height by dragging the border in design mode.

Next select the inner distinctcount of customerID field in each week row in both the dayofweek column and the total column->right click->format field->common tab->suppress->x+2 and enter:

whileprintingrecords;
numbervar cnt;
numbervar tot;
cnt := cnt + 1;
tot := tot + currentfieldvalue;
false

Then select the 0 in the total row (in both dayofweek and total column)->right click->format field->common tab->display string->x+2 and enter:

whileprintingrecords;
numbervar cnt;
numbervar tot;
totext(tot/cnt,0)//0 for no decimals

Finally, with the same {@0} summary selected in the total row and total column->right click->format field->border tab->background->x+2 and enter:

whileprintingrecords;
numbervar cnt := 0;
numbervar tot := 0;
crnocolor

This last formula resets the calculations for each day of week.

-LB

 

[Dross]

Thanks for the help. Seems more complex than it should have to be, but SAP tends to do that. I did this and as you can see it duplicated the sums of the customer counts instead of averaging, but at least I am a step closer. I attached the screen shot. The one variable I did not mention (not sure it makes a difference) is I have this crosstab in the group footer by location

 

[lbass]

I did test this and it worked, so I think you implemented incorrectly. Maybe restate the exact steps you took so I can troubleshoot. Or ask questions about each step where you might not be sure you understood.

-LB

 

[Dross]

I think your steps are pretty clear, but maybe I am misreading them. Sorry about the shots but I think it's the best way to step through it with you.
Instead of {@0} I created one called PlaceHolder

I then went to the first customer count (831) and total (5650) and followed your steps of: Next select the inner distinctcount of customerID field in each week row in both the dayofweek column and the total column->right click->format field->common tab->suppress->x+2 and enter:

Then I went to PlaceHolder field showing a 0 and went to the steps: Then select the 0 in the total row (in both dayofweek and total column)->right click->format field->common tab->display string->x+2 and enter:

And finished with: Finally, with the same {@0} summary selected in the total row and total column->right click->format field->border tab->background->x+2 and enter:

I feel like I followed the steps, but maybe I missed something. Here is what it looks like in design mode. Nothing really to it. Thanks for your help! For something that sounds like an easy request, it is turning out to not be so easy.

 

[lbass]

It looks like you don't have a row field and that instead you are just using the total by placing the crosstab in a group section. Instead, place the crosstab in the report header or report footer and add the field you are grouping on as your row field in the crosstab expert. You MUST have the row field in order to calculate the average. The first formula should be created in the formatting area for the inner row field and the row total, not at the column total level.

I would expect that your rowfield would be based on week, i.e., on a formula like {@week}, since you want the average across weeks:

Datepart("ww",{table.date})

-LB

 

[Dross]

Still haven't gotten it to work, but I am betting it is how I am implementing your instructions. Working on it and will update one way or another. I appreciate your help on this!

 

[Dross]

Still working on it, but I am still getting unexpected results. I may not be clear on where you are saying "The first formula should be created in the formatting area for the inner row field and the row total, not at the column total level." Moved the Cross Tab to the report footer and added the location to a row (seen in 2nd picture)

 

[Dross]

Whoops, 2 of the same image. one of them should be:

 

[lbass]

You're looking for the average in the total row--across locations, right? So for Sunday, the average of 952, 842, and 856, right?

By inner cell, I mean select 952 for example--this will select all inner cells and also select the row total, 6427.

The second display formula only goes in the column total row in the formatting area for the placeholder. Reset also belongs on cells in this row.

-LB

 

[Dross]

I went through this from the beginning again and I am getting the same results, the "Avg" field (@PlaceHolder) is duplicating the count above it instead of averaging. There has to be a factor I am not seeing that is different than what you tested successfully. I have this in the report Footer now so the other group sections above should not be interfering. I had to hide my location names for work reasons, but you can see the image below.

 

[lbass]

Not sure why you are showing values for the placeholder in the inner cells. Nothing is formatted for that summary in the inner cells. Only the bottom row placeholder should have the display string formula and also the reset formula in the background formula area. You can actually suppress and resize the inner cell placeholder summary to minimize its height.

-LB