Sum on Set Analyses question

[andre_ficken]

Hello, I have the following question: I am reporting on a table with open invoices the invoice has a GL bookingdate. I also have a table with all dates in it. What I would like to do is a historical open invoice report using the following:

sum(invoiceamount) but only when isnull(getfieldselections(bookingdate)) or bookingdate <= getfieldselections(bookingdate).

The latter 2 condtions I would like to wrap in a set analyses. Is this possible? I have been testing and trying for a while now, without much luck sofar. Any help would be appriciated.

Txs,André

[Anonymous]

don't know for sure ... but something like this might be in the right direction?

sum( {< bookingdate = {'<=$(getfieldselections(bookingdate))' ,$(nullvalue()) } >} invoiceamount)

[andre_ficken]

I have some additional info: I tried to get this using an if in the expression.
For the amounts that worked fine, the only thing I noticed that the subtotal is not working anymore and displays a '-'.

[Anonymous]

[quote user="André ***"]subtotal is not working anymore and displays a '-'

I did have the same problem before ... this has to do with combined expressions, in this case the subtotal has no clue what to do.

did you try something like the following (the if you talked about):

sum( if ( isnull ( getfieldselections(bookingdate) ) or bookingdate <= getfieldselections(bookingdate) , invoiceamount )

[andre_ficken]

It is definitely more complex. The set analyses does not work. Besides that I need to get a set for the invoiceamounts based on invoice date, a set for the invoice payments based on the payment date and finally subtract the 2. So it cannot contain null values, otherwise the result is null and the totals do not work. It is essential that the line and column total calculations work correctly.

[Anonymous]

Maybe a sample might help for the understanding of the problem...

[andre_ficken]

I cannot post any data here, since it is company confidential. Mail me (andre.***@viafi.com) your direct address so I can mail a sample to you directly outside this forum. Txs,André

[Anonymous]

André,

You could also try to give a sample with 'other' data ... just to emphasis what the problem is.
Or try to explain with some table samples what you have and what the data should deliver.

rgrds,

Anita

[andre_ficken]

I have created a sample file with some explanation on screen. Hopefully you can help me out here.

Txs,André

[Anonymous]

As mentioned in one of the prior suggestions the SUM should be taken out of the IF structure.

I added the correct sum in the sample.

SUM(
 if ( isnull ( getfieldselections( [Boekdatum (K)] ) )
 or [Factuur Boekdatum] <= getfieldselections([Boekdatum (K)] )
 , Factuurbedrag, 0 )
)

instead of

if ( isnull ( getfieldselections( [Boekdatum (K)] ) )
or [Factuur Boekdatum] <= getfieldselections([Boekdatum (K)] )
, sum(Factuurbedrag), sum(0) )

rgrds,

Anita