Zakir Sharief Posted April 27, 2021 Share Posted April 27, 2021 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 More sharing options...
Gaia Paolini Posted April 28, 2021 Share Posted April 28, 2021 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 More sharing options...
Zakir Sharief Posted April 28, 2021 Author Share Posted April 28, 2021 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 More sharing options...
Gaia Paolini Posted April 28, 2021 Share Posted April 28, 2021 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 Link to comment Share on other sites More sharing options...
Zakir Sharief Posted April 28, 2021 Author Share Posted April 28, 2021 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now