Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
caccio88
Creator II
Creator II

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

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_IDDue_DatePayment_DateCLOSED_OPENValue
109/07/201707/11/2017 CLOSED                  5
210/09/201710/01/2018 CLOSED 10
311/11/201711/01/2018 CLOSED 20
409/05/201707/08/2017 CLOSED 15
509/01/2018OPEN15
610/12/2017 OPEN10

...and my final goal!

 

05-1706-1707-1708-1709-1710-1711-1712-1701-18
OPEN1015
CLOSED              15               15               20               20               15               15 3030

Cuold someone help me, please?

stalwar1

gwassenaar

thank u in advance!!!

Fil

5 Replies
caccio88
Creator II
Creator II
Author

pcammaert

cleveranjos

jontydkpi

hello guys! could anyone help me?

thank you all

Clever_Anjos
Employee
Employee

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

‌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
Creator II
Creator II
Author

stalwar1‌ any idea about that? Thank you.

caccio88
Creator II
Creator II
Author

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

this is the final table:

05-1706-1707-1708-1709-1710-1711-1712-1701-18
OPEN1015
CLOSED              15              15              20                5              15              153030

and I attach the fixed excel.