How to calculate the invoices which are backlog in different months

[Zakir Sharief]

I have below set of data(sample below) in which i need to identify which all of the invoices are backlog in different months. Entitlement date is when the invoice is supposed to be invoice to be issued. If any invoice which is not issued in the same month falls under backlog. This is easy to calculate.

However, in several cases the invoice is entitled to be issued in Jan and only issued in March. So this invoice needs to be counted twice as one in Jan and one in Feb. Because this was only issued in March. This count will provide the user an understanding that for a month how many invoices are backlog and how many needs to be cleared.

I am unable to perform this calculation, could you please help in finding the logic,I am ok to have multiple columns bymonths and later i can unpivot the data to visualize.

Thanks,

Zak

 

 

 

 

Invoice

 

Entitlement Date

Date Invoice Issued

 

 

Invoice 1

04-01-2021

25-02-2021

 

 

Invoice 2

09-01-2021

26-02-2021

 

 

Invoice 3

12-01-2021

26-03-2021

 

 

Invoice 4

15-01-2021

11-02-2021

 

 

Invoice 5

16-01-2021

17-02-2021

 

 

Invoice 6

21-01-2021

26-02-2021

 

 

Invoice 7

23-01-2021

30-03-2021

 

 

Invoice 8

03-02-2021

30-03-2021

 

 

Invoice 9

04-02-2021

30-03-2021

 

 

Invoice 10

05-02-2021

24-03-2021

 

 

Invoice 11

05-02-2021

24-03-2021

 

 

Invoice 12

22-02-2021

01-03-2021

[Gaia Paolini]

can you elaborate on the data structure you want to achieve If you want a backlog and a month that looks like you want to generate a new table, unless you want an extra column for each month showing which months the invoice (row) is backlogged Or maybe a single extra column showing the list of backlog months. Knowing how you want to visualize it would help too.

[Zakir Sharief]

Hi,

 

I am currently using excel as data structure to build the POC, but will eventually connect to Greenplum. I would need backlog for every month, as i am planning to have cross table as visualization and would like to show the backlog invoices by month. For example i have one invoice which is entitled to be issued in Jan'21 and it was only issued in Mar'21. Then this invoice is open for the month of Jan and Feb. In the cross table it should show me 1 for Jan and 1 for Feb and 0 for Mar, as this is issued in March.

 

Please let me know if this clarifies your question.

 

Thanks,

 

Zak

[Gaia Paolini]

I think you meant excel /greenplum as data source I was asking about the structure of the data table you want to use. Maybe you can upload a screenshot of what you would like the resulting table to look like

[Zakir Sharief]

The data structure I am looking at it is something similar below. As I need to show the count of invoices in crosstable. I am planning to have a structure as attached screenshot. I just need the 12 months as column and calculation to populate the invoice count, I can use tranformation to plot this later or make the data in the required format. I am looking for the similar table in Spotfire once i have the calculation in place. Currently I am not able to derive the calculation to show count of invoices by months.