Excluding Fields from Aggr function

[engr_farhanqadr]

Dear Folks, 

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

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)
)

I have tried using {<Year=,YearlyQuarter=>} in inner sum but no luck , please help out.

Thanks

[tresB]

 have tried using {<Year=,YearlyQuarter=>} in inner sum but no luck , please help out.

Try putting the same in outer sum and aggr() as well.

[sunny_talwar · Accepted solution]

Try this and see if this works

Sum({<Year, YearlyQuarter>} Aggr(If( (
(Sum({<Year, YearlyQuarter>} SALES_GROSS_PREMIUM- SALES_AMOUNTPREMIUMREVERSALS - SALES_BROKER_FEES-SALES_COMMISSION))
*
( RangeMin(Only({<Year, YearlyQuarter>} 1), (RangeMax((Num(GetFieldSelections(OpeningMonth))-Num(Only({<Year, YearlyQuarter>} SALES_COVERAGEMONTH))),0 ) /365.25)) )

)=0, 0,
( Sum({<Year, YearlyQuarter>} SALES_GROSS_PREMIUM- SALES_AMOUNTPREMIUMREVERSALS - SALES_BROKER_FEES-SALES_COMMISSION))
- ((Sum({<Year, YearlyQuarter>} SALES_GROSS_PREMIUM- SALES_AMOUNTPREMIUMREVERSALS - SALES_BROKER_FEES-SALES_COMMISSION))
* RangeMin(Only({<Year, YearlyQuarter>} 1), (RangeMax((Num(GetFieldSelections(OpeningMonth))-Num(Only({<Year, YearlyQuarter>} SALES_COVERAGEMONTH))),0 ) /365.25)))
), SALES_COVERAGEMONTH)
)

[engr_farhanqadr]

Thanks for your response but exclusion filters should be applied only with SALES_COVERAGEMONTH
RangeMin(1, (RangeMax((num(GetFieldSelections(OpeningMonth))-num(only({<Year, YearlyQuarter>}SALES_COVERAGEMONTH))),0 ) /365.25))

it is working now.