Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Nachscratch
Contributor
Contributor

Aggr() of independent data in a pivot table

Hi There,

Within a pivot table, I am struggling to create a virtual table with Aggr() from data that uses both:

- filtered data from the pivot table

- data from a table that is not linked to the above

The problem is as follows. I have 2 tables that are not linked:

Table1 values:

[ID];[Value]

A;1

A;2

A;3

B;4

B;2

B;6


Table 2:

[Event]

0

1

2

3

4

 

The pivot table I am trying to create should show:

DIMENSION= [ID], i.e. A;B

Measure: SUM(Pow([Event],avg([Value])), in this case, for A: 0^2 + 1^2 + 2^2 + 3^2 + 4^2 and for B: 0*4 +1^4 + 2^4+3^4+4^4

 

I tried writing the Measure as :

Sum(Aggr(

Pow([Event],avg([Value])),

[Event])

but it doesn't work at all. It seems that it cannot built that aggregate based on [Event] as parameter since Table 2 is not linked/concatenated/joined to Table 1.

I've tried using "total" in all sort of combinations, but the only one that yields a result is:

Sum(total Aggr(

Pow([Event],avg([Value])),

[Event])

However, in this case the avg([Value]) is no longer per ID (2 for A and 4 for B) but the overall average (3).

Any ideas on how to bridge this problem? how can I use both [Events] that is independent and [Value] that is filtered per [ID]?

thanks!

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try this

Sum(Aggr(
	Pow([Event], Avg([Value])),
ID, [Event]))

View solution in original post

1 Reply
sunny_talwar

Try this

Sum(Aggr(
	Pow([Event], Avg([Value])),
ID, [Event]))