7 Replies Latest reply: Jan 11, 2013 7:24 AM by Nirav Bhimani

# Help with Table Expression?

I have a table with all my outstanding invoices(Customer, Inv Date and Amount) . I am trying to create an age analysis for this - current 30 days and 60 +.

When I do a table with invoice as the dimension it works but when i want to summarize by customer it only calcs the ones with only one invoice.

The expression I use is the following:

if([Ageing] <= 31, Sum(InvoiceBal2), '') etc.

I would assume the reason the summary per customer doesnt work is that it doesnt know what the ageing is per customer when there are multiple invoices with different ageing.

Any suggestion how to overcome this?

• ###### Re: Help with Table Expression?

Include the condition into the aggregation:

= Sum( if([Ageing] <= 31, InvoiceBal2))

• ###### Re: Help with Table Expression?

=Sum({<[Ageing]<=31>}InvoiceBal2) ?? like this

• ###### Re: Help with Table Expression?

If you want to use a set expression instead the if, try:

=Sum( {<[Ageing] = {"<=31"} >} InvoiceBal2)

• ###### Re: Help with Table Expression?

Hi,

Use aggr fuction with it.

Regards,

Nirav Bhimani

• ###### Re: Help with Table Expression?

Hi

I dont know the aggr function - can you give me examble please

Does it make a difference if ageing is a calculated field and how whould you change my expression?

if([Ageing] <= 31, Sum(InvoiceBal2), '')

• ###### Re: Help with Table Expression?

Does it make a difference because ageing is a calculated field? I get it from deducting the invoicedate from a variable.

Nirav I am not to sure how the aggr function works - can you give me an examble?

• ###### Re: Help with Table Expression?

Hi,

Syantax Aggr(Expression, GroupByFiledName)

Aggr( Sum( {<[Ageing] = {"<=31"} >} InvoiceBal2) , customer )

Regards,

Nirav Bhimani