Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

arsenal83
Not applicable

Sum over the period, sum if

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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Not applicable

Re: Sum over the period, sum if

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.


talk is cheap, supply exceeds demand
6 Replies
Gysbert_Wassenaar
Not applicable

Re: Sum over the period, sum if

See attached qvw.


talk is cheap, supply exceeds demand
arsenal83
Not applicable

Re: Sum over the period, sum if

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
Not applicable

Re: Sum over the period, sum if

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.


talk is cheap, supply exceeds demand
arsenal83
Not applicable

Re: Sum over the period, sum if

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
Not applicable

Re: Sum over the period, sum if

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.


talk is cheap, supply exceeds demand
arsenal83
Not applicable

Re: Sum over the period, sum if

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.

Community Browser