Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: Help with Table Expression?

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

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

7 Replies
MVP
MVP

Re: Help with Table Expression?

Include the condition into the aggregation:

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

nirav_bhimani
Contributor III

Re: Help with Table Expression?

Hi,

Use aggr fuction with it.

Regards,

Nirav Bhimani

Not applicable

Re: Help with Table Expression?

 

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

Not applicable

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), '')

MVP
MVP

Re: Help with Table Expression?

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

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

Not applicable

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?

nirav_bhimani
Contributor III

Re: Help with Table Expression?

Hi,

Syantax Aggr(Expression, GroupByFiledName)

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

Regards,

Nirav Bhimani 

Community Browser