Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
valeling
Contributor

Expression not considering dimensions in Pivot Table

Hey good morning!

I'm trying to make a weighted average in a Pivot Table and the expression does not conform to the table dimensions, the values are repeated for all Customers, example of the problem:

media cliente.png

The expression that I use to calculate the weighted average is:

=sum(total {<[Validacao Core] = {1}>}  Aggr( Count (Notification) * Core, Core ) ) /  sum(total {<[Validacao Core] = {1}>}  Aggr( Count (Notification), Core ) )

In another text object, the expression works perfectly and when I apply the Customer filters the values are recalculated there.

Otherwise, if I filter any client, the value displayed in the table is the correct one, for example:

valor correto.png

I tried to include the break in the expression through <[Customer Name]> in front of the TOTAL identifier in the sum function, so the expression looks like:

=sum(total <[Customer Name]> {<[Validacao Core] = {1}>}  Aggr( Count (Notification) * Core, Core) ) /  sum(total <[Customer Name]>   {<[Validacao Core] = {1}>}  Aggr( Count (Notification), Core) )

But in this way, some values are null and other clients have values that do not make sense, as shown in the picture below, any suggestions on where I'm wrong?

com customer name.png

In addition, I would like to make a calculation of standard deviation of this weighted average, the concepts to be applied are the same as the solution to this problem?

Thanks in advance!

1 Solution

Accepted Solutions
Partner
Partner

Re: Expression not considering dimensions in Pivot Table

Hi Paulo. I have noticed two issues in your expression:

  • First, I think you should Aggregate also by your table dimensions. You aggregate by Core but you need to agregate by Customer name too.
  • Second, the column Media takes the same value for all customers because you used "TOTAL" in your sums

Taking the previous into account, try the following expression:

=sum({<[Validacao Core] = {1}>}  Aggr( Count (Notification) * Core, Core, [Customer Name] ) ) /  sum({<[Validacao Core] = {1}>}  Aggr( Count (Notification), Core, [Customer Name] ) )

Hope it helps. Bests,

Jaime.

5 Replies
ogautier62
Valued Contributor II

Re: Expression not considering dimensions in Pivot Table

Hi,

could you provide some rows example please ?

first thing I see is to add customer name before core in aggr

for null values, to see with an example, maybe necessary to add nodistinct in aggr

regards

valeling
Contributor

Re: Expression not considering dimensions in Pivot Table

Hello, thanks for your answer!

Sure, it follows as an attachment...

I tried to put <[Customer Name]> before core in aggr and it doesn't work

Partner
Partner

Re: Expression not considering dimensions in Pivot Table

Hi Paulo. I have noticed two issues in your expression:

  • First, I think you should Aggregate also by your table dimensions. You aggregate by Core but you need to agregate by Customer name too.
  • Second, the column Media takes the same value for all customers because you used "TOTAL" in your sums

Taking the previous into account, try the following expression:

=sum({<[Validacao Core] = {1}>}  Aggr( Count (Notification) * Core, Core, [Customer Name] ) ) /  sum({<[Validacao Core] = {1}>}  Aggr( Count (Notification), Core, [Customer Name] ) )

Hope it helps. Bests,

Jaime.

Partner
Partner

Re: Expression not considering dimensions in Pivot Table

This is what I obtain, by the way:

Result.png

valeling
Contributor

Re: Expression not considering dimensions in Pivot Table

Hi Jaime...

First of all, thank you for your answer, this is exactly what I needed..

I thought that putting <[Customer Name]> in the Set Analysis Expression in sum() function it would break my results by Customer...

Thank you man, this helped me a lot!