Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

How to calculate the invoices which are backlog in different months


Zakir Sharief

Recommended Posts

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...