Its hard to know where to start with such an open ended post. I think you would get more help if you could be more specific: what have you tried, what do you think might be going wrong, what you expected. I don't know what your question is.
I for one, would be happy to help, but I don't have enough to go on.
Jonathan
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
You can see the code and logic of what I am trying to do. What I need is help as to how I can include all the above into one chart. One of the problems is my joins aren't perfect, in that when I select a date in BSAD, then values for BSAK are null. I maybe need to join by date as I am using posting and clearing dates on different tables.
In short I need guidance about how to go about this, how to join my tables and the best way to create my calculations (in the script or in the QVW's charts).
You will be aware of the tables BSAD, BSID, etc... as referred to in the table posted previously. I need to create Debtor and Creditor Day KPI's based on a point in time. So for each month or period I need to calculate the total closed, total closed within 30 days and the total open items to calculate the KPI. The total closed is easy because I am only looking at BSAK or BSAD, but to get the open items I have to read both closed and open items tables, in other words BSID or BSIK as well. Because I'm looking at open and closed tables simultaneously I'm finding it difficult to incorporate selections over both tables, as any selection on the open items tables negates any on the closed items tables and vice versa. I am struggling about how to go about doing this in QlikView. Have you done something similar? Please don't hesitate for more clarification.
You want to get all open bill details From BSID. For You have to delete one by one bill on FIFO Basis. And For all claered Item You can easily get details from BSAD.
Partial Payment Conecept is there in BSID.
So for Non Cleared Item You have to Follow FIFO Method.
The QlikView logic would be very helpful - thanks. Getting the cleared items from BSAD is easy but trying to include the open items from BSID in the same chart is difficult, due to BSAD restrictions on the chart. Also, because this is an historic report, I need to use BSAD also to work out what was open at a point in time as well as what's in BSID.
The code and logic of what I am trying to do is in my initial post. I am basically trying to do a total count on BELNR from BSAD/BSID/BSAK/BSIK for the creditor/debtor KPI's. Let me know if you need more information. Thanks.
I don't know if you are still looking for an answer, but I just came across this post and am wondering why you don't do the whole thing in the script? Create a common fact-table by loading first BSAD with a field "Indicator" = "Closed", then load BSID with the condition "where not exists(BSAD.BELNR, BSID.BELNR) and the "Indicator" set to "Open" - and then it should be easy to do the calculations with basic set analysis-functions. Then do the same with BSAK/BSIK (or even put them in the same facts-table and set a second Indicator to Debtor/Creditor - then it would be easier to use a common calendar, as well). If you do nightly reloads, you can even pre-calculate the over-30-days-open-KPIs in the script.
Hope this helps you - I'm not sure about the table-structures, so I'm not a 100% sure it will work exactly like this, but as far as I understood the relations between the tables, this would be my approach.
