Sum over the period, sum if

[arsenal1983]

Hi QV experts,

I have one case to solve. My figures describe  invoices of once company. So I have:

• invoice number,
• amount,
• date of invoice,
• due date (the date that payment should be done)
• the settlement date of the invoice (the real closing date of the invoice) can be null (if not payed yet) or before after or equal the due date.

I need to provide the sum of receivables over the period divided into overdue and term receivables (not required on certain day).

In xls it is something like below.

Any ideas how to do this in qlickview?

[Gysbert_Wassenaar]

See attached qvw.

[arsenal1983]

Thanks for your reply but it is not what I need. I need to show what is the AMOUNT of unpaid invoices devided into overdue and term values.

[Gysbert_Wassenaar]

Perhaps you should consider following a Qlikview training. If you don't know how to replace one field name with another field name or replace one aggregation function with another aggregation function in an expression you're not going to be very successful as a Qlikview developer.

[arsenal1983]

Dear Gysbert, thanks for your advice. Perhaps you should consider reading comprehension course. Your solution has no sense (it's not only aggregation issue). Your chart:

Could you explain what is on expression axe (count of what?)? In my file there were just 15 records, while your algorithm count to 40 docs. To your info - I have done this on my own but looking for more optimal solution

[Gysbert_Wassenaar · Accepted solution]

Could you explain what is on expression axe (count of what?)? In my file there were just 15 records, while your algorithm count to 40 docs

That's just me being an idiot. Unfortunately a reading comprehension course will not fix that. What I did was use the Week field as dimension and a count(...). What I should have done, apart from using sum(VALUE) instead of count(...), is either use REF_DATE as dimension or use a count(distinct ...). Because I didn't the count is off because each invoice is counted for each day it is overdue in a week instead of counted only once for the entire week.

[arsenal1983]

Clever monkey Everything works grate one more modification in script:

WHILE ISSUE_DATE + IterNo() -1 <= alt(SETTLEMENT_DATE, today()); - should be just < (not <=) as we should not report invoices when settlement_date = ref_date (we report end day situation).

Thanks, for help.