Expression help

[#1]

Having some trouble with this expression

=sum(if(min([Created on])=min([Picking date]), count(DISTINCT(DOC_NUMBER)), NetWorkDays(min(date([Created on])), min(date([Picking date])))))

This does not work correctly, however when I take out the 'sum' function, the expression is correct on line level but the total is wrong...

Im confused...

Thanks

[swuehl · Accepted solution]

You can't embed an aggregation function like min() into another aggregation function like sum() without using advanced aggregation.

Do you want something like sum of rows (in a pivot table? in a straight table, there is also a total mode to do so)?

Then try something like

=sum(

aggr(

if(min([Created on])=min([Picking date]), count(DISTINCT(DOC_NUMBER)), NetWorkDays(min(date([Created on])), min(date([Picking date]))))

,YourChartDimensionsListedHere)

)

See also sum-of-rows in pivot tables in the Help.

Regards,

Stefan

[#3]

That is correct, I need to sum rows in a pivot table...

In your expression, what needs to go into the 'YourChartDimensionsListedHere'

Thanks

[swuehl]

Your charts dimensions, comma separated (note: aggr() will not take any calculated dimensions, so you can use only field names).

For example, if you are using Client and Store field names as dimensions:

=sum(

aggr(

if(min([Created on])=min([Picking date]), count(DISTINCT(DOC_NUMBER)), NetWorkDays(min(date([Created on])), min(date([Picking date]))))

,Client, Store)

)

Please check also the Help for more details.

Regards,

Stefan