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: 
oscarvictory
Contributor III
Contributor III

Nested aggregation not as expected

Hi all, 

Hope somebody can help me out.

My data:

Total Sales by Customer & Month, plus weighted average:

Imagen 1.jpg

What I need:

Imagen 5.jpg

 

Data from where I get the weighted average: 5,48

Imagen 2.jpg

LOAD JAN as Coste, TYPE,

If (JAN<> 0, 'JAN') as MONTH

FROM ....

;

LOAD FEB as  Coste, TYPE,

If (FEB<> 0, 'FEB') as MONTH

FROM ....

;

LOAD MAR as Coste, TYPE,

If (MAR<> 0, 'MAR') as MONTH

FROM ....

;

Question is that I need to display the weighted average into the corresponding column disregarding the Customer's Sales.

 

The formula I tried did not work for me because takes into account the Sales:

Sum(Aggr((Sum(distinct{<TYPE ={ 'Price1'}>} Coste) * Sum(distinct {<TYPE ={'Price2'} >} Coste)), MONTH)) 

/

Sum(Aggr(Sum(distinct {<TYPE ={'Price1'} >} Coste), MONTH))

 

Many TKS.

1 Reply
oscarvictory
Contributor III
Contributor III
Author

For those who were wondering about the solution, it was as easy as to add the Customer dimension:

Sum(Aggr((Sum(distinct{<TYPE ={ 'Price1'}>} Coste) * Sum(distinct {<TYPE ={'Price2'} >} Coste)), MONTH, CUSTOMER)) 

/

Sum(Aggr(Sum(distinct {<TYPE ={'Price1'} >} Coste), MONTH, CUSTOMER))