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: 
engr_farhanqadr
Creator
Creator

excluding fields from aggr function

Dear Folk, 

I am stuck at this point, I am using below formula.I want to exclude the impact of selecting years and yearly quarter

 

sum(Aggr(if( (
(sum(SALES_GROSS_PREMIUM- SALES_AMOUNTPREMIUMREVERSALS - SALES_BROKER_FEES-SALES_COMMISSION))
*
( RangeMin(1, (RangeMax((num(GetFieldSelections(OpeningMonth))-num(SALES_COVERAGEMONTH)),0 ) /365.25)) )

)=0,0 ,
( sum(SALES_GROSS_PREMIUM- SALES_AMOUNTPREMIUMREVERSALS - SALES_BROKER_FEES-SALES_COMMISSION))
- ((sum(SALES_GROSS_PREMIUM- SALES_AMOUNTPREMIUMREVERSALS - SALES_BROKER_FEES-SALES_COMMISSION))
* RangeMin(1, (RangeMax((num(GetFieldSelections(OpeningMonth))-num(SALES_COVERAGEMONTH)),0 ) /365.25)))
),SALES_COVERAGEMONTH)
)

 

Thanks,

2 Replies
NhanNguyen
Contributor III
Contributor III

Adding Year and Quarter into your set analysis to exclude user selections like this:

sum( {<Year= , Quarter= >}

Aggr(if( (
(sum(SALES_GROSS_PREMIUM- SALES_AMOUNTPREMIUMREVERSALS - SALES_BROKER_FEES-SALES_COMMISSION))
*
( RangeMin(1, (RangeMax((num(GetFieldSelections(OpeningMonth))-num(SALES_COVERAGEMONTH)),0 ) /365.25)) )

)=0,0 ,
( sum(SALES_GROSS_PREMIUM- SALES_AMOUNTPREMIUMREVERSALS - SALES_BROKER_FEES-SALES_COMMISSION))
- ((sum(SALES_GROSS_PREMIUM- SALES_AMOUNTPREMIUMREVERSALS - SALES_BROKER_FEES-SALES_COMMISSION))
* RangeMin(1, (RangeMax((num(GetFieldSelections(OpeningMonth))-num(SALES_COVERAGEMONTH)),0 ) /365.25)))
),SALES_COVERAGEMONTH)
)

 

Hope it helps!

engr_farhanqadr
Creator
Creator
Author

I have tried {<Year= , Quarter= >} at sum and also in inner sum as well but it does not work.