Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
arsenal1983
Creator
Creator

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

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

View solution in original post

6 Replies
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
arsenal1983
Creator
Creator
Author

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.


talk is cheap, supply exceeds demand
arsenal1983
Creator
Creator
Author

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

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
arsenal1983
Creator
Creator
Author

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.