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: 
adamh2404
Partner - Contributor II
Partner - Contributor II

AGGR Function Help.

Hello,

I'm having a problem correctly calculating a value based on an expression using the aggr function.

The data is as below:

                         Feb 2016          Feb 2017

Product ID     Qty      Cost        Qty      Cost

A                   null()    null()         2        13.85                               

B                    3        20.05       10        59.00

C                   18       102.13      38        201.09

D                    5         28.36      22        116.42

Now I need to divide the total Cost/Qty only where a product appears in both years.

So for my KPI for Avg Cost for 2017 the calculation would be:

(59+201.09+116.42)/(10+38+22) = 5.37

I have this as a starting point, however that is not doing the check for both years at product ID level and is therefore calculating my KPI across all 4 products rather than 3:

IF(

Sum({<[Ledger Year]={2017}>}[Sales Cost (GBP)])>0 and Sum({<[Ledger Year]={2016}>}[Sales Cost (GBP)])>0,

(Sum({<[Ledger Year]={2017}>}[Sales Cost (GBP)])

/

Sum({<[Ledger Year]={2017}>}[Sales Qty]))

,0)

Any help is appreciated.

1 Solution

Accepted Solutions
sunny_talwar

May be this

Sum({<[Ledger Year] = {2017}, [Product ID] = {"=Count(DISTINCT {<[Ledger Year]={2017, 2016}>} [Ledger Year]) = 2"}>} [Sales Cost (GBP)])

/

Sum({<[Ledger Year] = {2017}, [Product ID] = {"=Count(DISTINCT {<[Ledger Year]={2017, 2016}>} [Ledger Year]) = 2"}>} [Sales Qty]))

View solution in original post

5 Replies
Anonymous
Not applicable

try:

sum(aggr(

IF(

Sum({<[Ledger Year]={2017}>}[Sales Cost (GBP)])>0 and Sum({<[Ledger Year]={2016}>}[Sales Cost (GBP)])>0,

Sum({<[Ledger Year]={2017}>}[Sales Cost (GBP)])

),[Sales Cost (GBP)],[Sales Qty]))

/

sum(aggr(

IF(

Sum({<[Ledger Year]={2017}>}[Sales Cost (GBP)])>0 and Sum({<[Ledger Year]={2016}>}[Sales Cost (GBP)])>0,

Sum({<[Ledger Year]={2017}>}[Sales Qty])

),[Sales Cost (GBP)],[Sales Qty]))

agigliotti
Partner - Champion
Partner - Champion

try this expression below:

=Sum( Aggr(

IF(

Sum({<[Ledger Year]={2017}>}[Sales Cost (GBP)])>0 and Sum({<[Ledger Year]={2016}>}[Sales Cost (GBP)])>0,

(

Sum({<[Ledger Year]={2017}>}[Sales Cost (GBP)])

/

Sum({<[Ledger Year]={2017}>}[Sales Qty])

)

,0), [Product ID] ) )

sunny_talwar

May be this

Sum({<[Ledger Year] = {2017}, [Product ID] = {"=Count(DISTINCT {<[Ledger Year]={2017, 2016}>} [Ledger Year]) = 2"}>} [Sales Cost (GBP)])

/

Sum({<[Ledger Year] = {2017}, [Product ID] = {"=Count(DISTINCT {<[Ledger Year]={2017, 2016}>} [Ledger Year]) = 2"}>} [Sales Qty]))

adamh2404
Partner - Contributor II
Partner - Contributor II
Author

Hi,

Thanks but this doesn't work. This divides correctly at line level but then just sums the 2 answers rather than calculating the totals of each half of the expression and then dividing.

adamh2404
Partner - Contributor II
Partner - Contributor II
Author

Hi Sunny,

Yes this works. Many thanks for the solution!!