sum if / sum aggr

[inescastelhano]

Hi everyone,

I have the following expressions, both work fine separately. Now I need to subtract them, and have the equivalent to "sum of rows" that one can have in straight table. I think I should use aggregate, but it returns null when I use it. Could someone help me please?

Expression 1:

(min({<myYear=,myMonth=,myDay=,myDate=,[Date 1]={'<$(=date(min(myDate)))'},[Date 2]={'>=$(=date(min(myDate)))<=$(=date(max(myDate)))'}>}[Date 2])

(could also be achieved with max, sum or only)

Expression 2:

-(min(total{$}

([Data])))

Final expression:

sum(aggr(Expression 1 - Expression 2 , ID))

This final expression returns null.

Thanks in advance.

Inês

[sunny_talwar]

Try this may be:

Sum(Aggr(RangeSum(Expression1, -Expression2), ID))

[inescastelhano]

Hi sunindia,

Thanks for your reply. It works on the RangeSum level, but unfortunately, when I use the aggr function it returns null anyway.

I don't understand what I am doing wrong, since the expressions work fine separately.

[sunny_talwar]

Create a straight table with ID as dimension and RangeSum(Expression1, -Expression2) as Expression and see if it gives you numeric value for ID's

[inescastelhano]

Hi,

I have it already and still nothing...

[inescastelhano]

The first column is the ID, the second one is the correct value for each ID, but the sum is incorrect. However, if I use "sum of rows" instead of "expression total", it works.

[sunny_talwar]

For your second expression, can you add Sum next to Aggr like this: Sum(Aggr(....

Does it give you nulls still?

[inescastelhano]

Since the aggr returns null, the sum (aggr (...)) returns zero

[sunny_talwar]

Do you have a sample I can look at?

[inescastelhano]

Sure.

Here it follows. I am selecting the entire month of january, 2015, as it is in the image.

Thanks