Announcements
cancel
Showing results for
Did you mean:
Specialist

## Chart Aggr question

Hello,

i am having some problem with the Aggr formula in a chart, my scenario is:

i have a table with two dimensions; Company and Object and one expression which is the amount, now the amount can be positive or negative.

what i am trying to do is to add 2 expressions; the first one giving the sum of the positive amount and the second one showing the sum of the negative amount while displaying only the company as a dimension, that is why i need to aggr on the level of company, object.

to note that the amount is calculated from a variable

//get first the previous years

(Sum({<[Transaction Year]=,[Transaction Month Name]=,[Period Number]=,[Fiscal Year]=,[Transaction Year]={"<\$(=max([Transaction Year]))"}, [Act Group]={'CashBank'},[Object Account]=, [Object Account]={1211,1212}>}[Ammount USD])

//now until selected year and month

+Sum({<[Transaction Year]={\$(=max([Transaction Year]))},[Transaction Month Name]=,[Period Number]={"<=\$(=MAX([Period Number]))"}, [Act Group]={'CashBank'},[Object Account]=, [Object Account]={1211,1212}>}[Ammount USD]))

or else i would have just add sum({<Amount={'>=0'}>}Amount) as  the expression

so suppose i have the below table:

 Company Object Amount C1 O1 100 C1 O2 -10 C1 O2 123 C2 O2 10 C2 O1 -50

the result will be:

 Company Positive Negatvie C1 213 C2 10 -50

thanks.

2 Replies
MVP

Not very sure about your doubt. Do you mean that having issues to include '<Amount={'>=0'}>}' of set analysis into the existing expression? Otherwise, it looks fine for positive figures.

Specialist
Author

if you take the below example

 Company Object Amount C1 O1 100 C1 O2 -10 C1 O2 123 C2 O2 10 C2 O1 -50

for Company C1, Object O2 you have -10 and 123 so the sum will be 113 + 100 from C1,O1 under positive amount, so it is the sum of the amount by object, company. and not the sum of the positive or negative amount only.

as if you are doing the below (step by step):

first calculate the sum of amount by Company, Object:

 Company Object Amount C1 O1 100 C1 O2 113 C2 O2 10 C2 O1 -50

and then you will get for each company, the sum of positive amount and sum of negative amount and you will end up with the below

 Company Positive Negatvie C1 213 0 C2 10 -50
Community Browser