topic Excluding Fields from Aggr function in QlikView

Excluding Fields from Aggr function

engr_farhanqadr — Thu, 21 Feb 2019 11:40:31 GMT

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

Re: Excluding Fields from Aggr function

tresB — Thu, 21 Feb 2019 11:46:44 GMT

 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.

Re: Excluding Fields from Aggr function

sunny_talwar — Thu, 21 Feb 2019 12:16:52 GMT

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

Re: Excluding Fields from Aggr function

engr_farhanqadr — Fri, 22 Feb 2019 07:56:43 GMT

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.