Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

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
Highlighted
Valued Contributor II

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

maybe this:

AVG(TOTAL <ClientID> Sales)

your probably Need to replace ClientID with your dimensions

Highlighted
Contributor

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

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?

Highlighted
Valued Contributor II

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

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.

Highlighted
Contributor

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

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

Highlighted
Valued Contributor II

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

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

Highlighted
Contributor

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

I expected results should look like:

30.9 % ,  23.80 %,   25.86 %, etc.

But OK. Thank you for your attention!

Best wishes,

Evgeniy