Troubles with Aggr function

[maleksafa]

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.

[maleksafa]

anyone?

[#3]

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 )

  )

)

[maleksafa]

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.

[#5]

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

[#6]

I have the same case, any help ?

[#7]

Anas, see what i wrote in my reply