Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Show correct average values in the "total" row

Hello!

Target:

I need to show average values in the "total" row at the bottom by each column.

Description:

I've got a pivot table with two dimensions (one of them are calculated). I selected a "show partial sums" option to my calculated dimension so the "total" row appeared but the values were strange. How to show average values?  Screenshot is attached.

Dimensions:

1. Week Row = aggr(if( min(Date) <= Date(WeekEnd(min(Date))), Date(WeekStart(min(Date))) & '-' & Date(WeekEnd(min(Date))),  ClientID)

2. Week Column = Week

Expression:

sum(Sales) / sum( TOTAL <ClientID> Sales)

P.s. I can't do any change in the load script and change places for dimensions.

Best wishes,

Evgeniy

6 Replies
Specialist II

maybe this:

AVG(TOTAL <ClientID> Sales)

Anonymous
Not applicable
Author

Thanks for the response.

The wrong values in the total row appeared because they are based on the second dimension in a chart - Week

The expression: aggr( sum(Sales) / sum( TOTAL <ClientID> Sales) ,Week)  - give the same wrong "total" values in cells.

What do you mean it this case? How would it look like?

Specialist II

maybe

AVG(TOTAL <Week, ClientID> Sales)  or

aggr(AVG(TOTAL <ClientID> Sales) , Week)

it would be great if you share a sample qvw that we can test that.

Anonymous
Not applicable
Author

Hello, Tim Driller

These expressions didn't solve the problem (and they also changed a calculation way what's incorrect)

A sample qvw is attached.

But as a result I need to get a correct expression/tip (but not a qvw document).

Best wishes,

Evgeniy

Specialist II

Hi,

i don't find the right Expression for your case.

i think your expected results should look like this?

 Total 3,90% 17,90% 20,80% 22,00% 15,10% 20,40% 23,80% 15,90% 13,95% 12,00% 18,90%

With following Expression you can force another Expression to Display in total row.

If(RowNo()=0
,'TOTAL ROW EXPRESSION'
,
sum(Sales) / sum( TOTAL <ClientID> Sales)
)

Anonymous
Not applicable
Author

I expected results should look like:

30.9 % ,  23.80 %,   25.86 %, etc.

But OK. Thank you for your attention!

Best wishes,

Evgeniy

Community Browser