topic Help with Table Expression? in QlikView

Help with Table Expression?

Fri, 11 Jan 2013 11:23:35 GMT

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?

swuehl — Fri, 11 Jan 2013 11:27:31 GMT

Include the condition into the aggregation:

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

Re: Help with Table Expression?

nirav_bhimani — Fri, 11 Jan 2013 11:28:40 GMT

Hi,

Use aggr fuction with it.

Regards,

Nirav Bhimani

Re: Help with Table Expression?

Fri, 11 Jan 2013 11:44:41 GMT

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

Re: Help with Table Expression?

Fri, 11 Jan 2013 11:48:10 GMT

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?

swuehl — Fri, 11 Jan 2013 11:53:53 GMT

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

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

Re: Help with Table Expression?

Fri, 11 Jan 2013 11:54:25 GMT

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?

nirav_bhimani — Fri, 11 Jan 2013 12:24:27 GMT

Hi,

Syantax Aggr(Expression, GroupByFiledName)

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

Regards,

Nirav Bhimani