Transactions (invoices) vs. customer master data credit limits

[johnlauridsen]

Hi

I have an accounting database with a lot of unpaid invoices, where I would like to group these into an aging balance 0-7days, 7-30days, 30-60 and 60->

I have not done this in the script but in the charts.

One expression is:

Header 1	Header 2
= sum(IF(FORFALDSDATO>today()-30 and FORFALDSDATO<today()-7,FORFALDENFAKTURABELØB,0))	eq to duedate between 7-30 days

On the customer masterdata I have a field for the credit limit. Now I would like to have a chart with the sums of the different overdue invoices (per customer, per overdue age) and also the value of the credit limit. In the end I would like to be able to sum my total credit limit and the total of the customer balances (overdue and not due.

[#2]

Hi  John,

Use the intervalmatch function in script to create a column called interval which will be having values like 0-7 days, 7-30 days, 30-60days and so on.

In pivot chart use it as a dimension and then put it on the top to display it as a columns.

Hope this help,

Anosh

[johnlauridsen]

Thanks,

But will this help me in the fact that the credit limit is stores once per customer and has to be shown as expression in line with the new interval match?

/John

[#4]

Hi John,

Bring everything to a single table. Like this below. Use left join in Qlikview to bring new column in the invoice table.

CustomerId, CreditLimit, Invoice, Interval

1               , 1000,       , 200,    , 0-7 days

1               , 1000,       , 100,    , 0-7 days

1               , 1000,       , 150,    , 8-30 days

1               , 1000,       , 110,    , 31-60 days

1               , 1000,       , 250,    , 31-60 days

Now in expression use 'min(CreditLimit)' for CreditLimit 'sum(Invoice)' and Interval should be present in dimension.

then put Interval on top to display it as columns.

Hope this help,

Anosh