topic Re: Show correct average values in the "total" row in QlikView

Show correct average values in the "total" row

Anonymous — Thu, 18 Jan 2018 08:22:52 GMT

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

Re: Show correct average values in the "total" row

zhadrakas — Thu, 18 Jan 2018 08:44:09 GMT

maybe this:

AVG(TOTAL <ClientID> Sales)

your probably Need to replace ClientID with your dimensions

Re: Show correct average values in the "total" row

Anonymous — Thu, 18 Jan 2018 12:21:53 GMT

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?

Re: Show correct average values in the "total" row

zhadrakas — Thu, 18 Jan 2018 15:22:20 GMT

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.

Re: Show correct average values in the "total" row

Anonymous — Fri, 19 Jan 2018 06:54:20 GMT

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

Re: Show correct average values in the "total" row

zhadrakas — Fri, 19 Jan 2018 09:44:53 GMT

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)
)

Re: Show correct average values in the "total" row

Anonymous — Fri, 19 Jan 2018 11:19:38 GMT

I expected results should look like:

30.9 % , 23.80 %, 25.86 %, etc.

But OK. Thank you for your attention!

Best wishes,

Evgeniy