Announcements
cancel
Showing results for
Did you mean:
Partner - Creator II

## sum if / sum aggr

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.

Inês

1 Solution

Accepted Solutions
MVP

This expression seems to be working:

=Sum({<myYear=,myMonth=,myDay=,myDate= >} Aggr(Min({<myYear=,myMonth=,myDay=,myDate=,[Date 1]={'<\$(=date(min(myDate)))'},[Date 2]={'>=\$(=date(min(myDate)))<=\$(=date(max(myDate)))'}>}[Date 2])-Min(TOTAL [myDate]), [Nº de Episódio]))

11 Replies
MVP

Try this may be:

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

Partner - Creator II
Author

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.

MVP

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

Partner - Creator II
Author

Hi,

I have it already and still nothing...

Partner - Creator II
Author

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.

MVP

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

Does it give you nulls still?

Partner - Creator II
Author

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

MVP

Do you have a sample I can look at?

Partner - Creator II
Author

Sure.

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

Thanks

Community Browser