topic Re: sum of aggr() returns 0 in QlikView

sum of aggr() returns 0

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have an expression which counts difference between beneficiaries for selected year and its previous year.The expression calculates the sum of beneficiaries for this year sum of beneficiaries for previous year and the subtracts both the values to get difference.

The part which calculates sum of beneficiaries for selected year gives correct value.

But the part which calculates beneficiaries for previous year always evaluates to 0.

Part which calculates sum of beneficiaries for selected

sum({<Financial_Year = {'$(Selected_Year)'}, Quarter = {'$(Selected_Quarter)'}>} aggr(if(TYPE = 'ABC',count({<Financial_Year = {'$(Selected_Year)'}, Quarter = {'$(Selected_Quarter)'}>}distinct BENEFICIARY))

Part which calculates sum of beneficiaries for previous year(which always evaluates to 0)

sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}>}aggr(if(TYPE = 'ABC',count({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}>}distinct BENEFICIARY))

I have declared variables as follows

Selected_Year = GetFieldSelections(Financial_Year)

Selected_Quarter = GetFieldSelections(Quarter)

Previous_Year = GetFieldSelections(Financial_Year) - 1

My entire expression is as follows:

=if(GetFieldSelections(Quarter),

sum({<Financial_Year = {'$(Selected_Year)'}, Quarter = {'$(Selected_Quarter)'}>}aggr(if(TYPE = 'ABC',count({<Financial_Year = {'$(Selected_Year)'}, Quarter = {'$(Selected_Quarter)'}>}distinct BENEFICIARY)) -

sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}>}aggr(if(TYPE = 'ABC',count({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}>}distinct BENEFICIARY)),

sum({<Financial_Year = {'$(Selected_Year)'}>}aggr(if(TYPE = 'ABC',count({<Financial_Year = {'$(Selected_Year)'}>}distinct BENEFICIARY)) -

sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}>}aggr(if(TYPE = 'ABC',count({<Financial_Year = {'$(Previous_Year)'}>}distinct BENEFICIARY))

Any help will be appreciated

Re: sum of aggr() returns 0

sunny_talwar — Tue, 21 Aug 2018 17:27:37 GMT

Try this for previous year

Sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}>} Aggr(If(Only({1} TYPE) = 'ABC', Count({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}>} DISTINCT BENEFICIARY))

I think you might be missing the last part of the expression where you give the Aggr's dimension/s

Re: sum of aggr() returns 0

Anonymous — Tue, 21 Aug 2018 18:19:58 GMT

Yes I am sorry for that

Added the dimension REFID

Re: sum of aggr() returns 0

sunny_talwar — Tue, 21 Aug 2018 18:27:23 GMT

Did you try it with the change I said?

Re: sum of aggr() returns 0

Anonymous — Tue, 21 Aug 2018 18:34:55 GMT

Yeah tried it just now and its working .Can you please explain the effect of adding the part in red to the code

Re: sum of aggr() returns 0

sunny_talwar — Tue, 21 Aug 2018 19:44:05 GMT

Although other parts of the expression was ignoring selection in current year, but TYPE was not... which is why the expression was not working. Ignored all selection for TYPE as well using Only({1} ... ) and it worked