Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
engr_farhanqadr
Contributor

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
New Contributor III

Re: excluding fields from aggr function

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
Contributor

Re: excluding fields from aggr function

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