Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
maybe this:
AVG(TOTAL <ClientID> Sales)
your probably Need to replace ClientID with your dimensions
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.
your probably Need to replace ClientID with your dimensions
What do you mean it this case? How would it look like?
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.
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
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)
)
I expected results should look like:
30.9 % , 23.80 %, 25.86 %, etc.
But OK. Thank you for your attention!
Best wishes,
Evgeniy