topic Expression not considering dimensions in Pivot Table in QlikView

Expression not considering dimensions in Pivot Table

paulinhok14 — Wed, 25 Nov 2020 16:16:04 GMT

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:

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:

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?

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!

Re: Expression not considering dimensions in Pivot Table

ogautier62 — Wed, 22 Aug 2018 15:39:14 GMT

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

Re: Expression not considering dimensions in Pivot Table

paulinhok14 — Wed, 22 Aug 2018 17:34:35 GMT

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

Re: Expression not considering dimensions in Pivot Table

jaibau1993 — Thu, 23 Aug 2018 07:49:43 GMT

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.

Re: Expression not considering dimensions in Pivot Table

jaibau1993 — Thu, 23 Aug 2018 07:53:16 GMT

This is what I obtain, by the way:

Re: Expression not considering dimensions in Pivot Table

paulinhok14 — Thu, 23 Aug 2018 10:37:41 GMT

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!