topic Re: Troubles with Aggr function in QlikView

Troubles with Aggr function

maleksafa — Tue, 21 Oct 2014 10:15:58 GMT

Hi All,

I am having some difficulties achieving a result using the Aggr function.

Basically i am doing a summation on a field Balance_Referance_Currency for a particular mapping level while the Month is up to the month displayed in the grid.

so for Jan 2013 it is only Jan 2013, for March 2013 it is up to March 2013 (Jan + Feb + March).

in addition to that i want only to sum the balance (Balance_Referance_Currency ) aggregate on the ledger, address_book_code that are greater than 0.

Attached you will find a sample for the document and the desired result in the excel sheet.

To test the scenario. select first only Jan, you will see that for example the Current Assets field is 10403242.66, however when you click on feb the value for Jan is changing and it should not.

thx.

Re: Troubles with Aggr function

maleksafa — Tue, 21 Oct 2014 13:47:07 GMT

anyone?

Re: Troubles with Aggr function

Tue, 21 Oct 2014 13:59:02 GMT

Hi Malec,

I have added the MonthCalander level to the aggr function, which solved the amounts changing problem.

make sure to add it in both expression.

however, numbers are only the same as your excel spreadsheet for Jan, Feb and March differ.

does that solve your problem?

fabs(

sum(

{< Account_Mapping_Level_7 ={'CAL','PINV', 'CASH'}, CalanderMonth ={"<=$(=max(CalanderMonth))"} >}

aggr(

if (

sum({< Account_Mapping_Level_7 ={'CAL','PINV', 'CASH'}, CalanderMonth ={"<=$(=max(CalanderMonth))"} >}Balance_Referance_Currency) >=0,

sum({< Account_Mapping_Level_7 ={'CAL', 'PINV', 'CASH'}, CalanderMonth ={"<=$(=max(CalanderMonth))"} >}Balance_Referance_Currency),0

)

, Ledger , CalendarYear, CalanderMonth,Address_Book_Code )

)

Re: Troubles with Aggr function

maleksafa — Tue, 21 Oct 2014 14:07:14 GMT

hi,

this only solve the problem of numbers changing but i am not getting the result that i want (in the excel).

because we you add the aggregation on the month level it will evaluate the amount of this month if it is positive and take it. however if you take for example in Feb, it should evaluate not Feb amounts alone but the amounts of Jan + Feb combined on Ledger and Address book code level, if this sum is positive it will be added.

Re: Troubles with Aggr function

Tue, 21 Oct 2014 14:19:19 GMT

in that case i would suggest that you add an above() function like so:

=if(isnull(above([Current Assets ])),0,+above([Current Assets ]))+

fabs(

sum(

{< Account_Mapping_Level_7 ={'CAL','PINV', 'CASH'}, CalanderMonth ={"<=$(=max(CalanderMonth))"} >}

aggr(

if (

sum({< Account_Mapping_Level_7 ={'CAL','PINV', 'CASH'}, CalanderMonth ={"<=$(=max(CalanderMonth))"} >}Balance_Referance_Currency) >=0,

sum({< Account_Mapping_Level_7 ={'CAL', 'PINV', 'CASH'}, CalanderMonth ={"<=$(=max(CalanderMonth))"} >}Balance_Referance_Currency),0

)

, Ledger , CalendarYear, CalanderMonth,Address_Book_Code )

)

However I do not get the same values as in your excel spreadsheet

Re: Troubles with Aggr function

Wed, 22 Oct 2014 07:39:30 GMT

I have the same case, any help ?

Re: Troubles with Aggr function

Wed, 22 Oct 2014 08:02:29 GMT

Anas, see what i wrote in my reply