Invoice Analysis_Different view in the same table, considering different time v

[caccio88]

Hello everyone,

i'm trying to replicate this chart of my attached excel in QV... but without success.

What I'd like to do is to consider in the same table the value for open and closed invoices.

- Open invoices follow the due date cause they haven't got payment date so they will be in the same month-year of their due date.

- Closed invoices follow different variables... they have to be considered in the previous month-year of their payment date and then considered until the month-year of their due date.

Am I clear enough?

That's my data...

INVOICE_ID	Due_Date	Payment_Date	CLOSED_OPEN	Value
1	09/07/2017	07/11/2017	CLOSED	5
2	10/09/2017	10/01/2018	CLOSED	10
3	11/11/2017	11/01/2018	CLOSED	20
4	09/05/2017	07/08/2017	CLOSED	15
5	09/01/2018		OPEN	15
6	10/12/2017		OPEN	10

...and my final goal!

	05-17	06-17	07-17	08-17	09-17	10-17	11-17	12-17	01-18
OPEN								10	15
CLOSED	15	15	20	20	15	15	30	30

Cuold someone help me, please?

stalwar1‌

gwassenaar‌

thank u in advance!!!

Fil

[caccio88]

pcammaert‌

cleveranjos‌

jontydkpi‌

hello guys! could anyone help me?

thank you all

[Clever_Anjos]

The classical solution for your need is buildind a Canonical Table please read this post  Canonical Date

it should be self-explanatory.

Please come back if you don´t solve your isseu

[caccio88]

‌thank you clever. I tried with a solution like something from that post, but it’s not the exact goal i‘m looking for cause as you can see from my excel calculation we have to create something like new data for the months where aren’t data from the payment date to the due date (i.e. for an invoice of 100$ payed in june and with a due date in March, what i want to obtain is 100 in may and 100 in Apri that doesn’t exist at the moment).

i don‘t Know if I added some info for the solution of my problem...

cleveranjos‌

[caccio88]

‌stalwar1‌ any idea about that? Thank you.

[caccio88]

I'm sorry... ther was a mistake in the solution in excel...

this is the final table:

	05-17	06-17	07-17	08-17	09-17	10-17	11-17	12-17	01-18
OPEN								10	15
CLOSED	15	15	20	5	15	15	30	30

and I attach the fixed excel.