Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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 |