Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I wish to consult you if it is correct in set analyses.
I have 2 tables in QlikView, both related to the financial/invoicing process.
[Table called 'FI' (or precisely BKPF-BSEG in SAP) ]
| FiId | RC | FiDate | FiMonth | Batch | Amount |
|---|---|---|---|---|---|
| 1 | Cost | 1/1/2015 | Jan15 | 1 | 1€ |
| 2 | Revenue | 6/2/2015 | Feb15 | 1 | 3€ |
| 3 | Cost | 3/2/2015 | Feb15 | 1 | 1€ |
| 4 | Revenue | 13/2/2015 | Feb15 | 1 | -3€ |
| 5 | Revenue | 13/2/2015 | Feb15 | 1 | 3€ |
*Tokens are:
FiId = primary key, generated.
RC = either revenue or cost category.
Date = transaction date.
FiMonth = dual value, MonthName(FiDate).
Batch = product Identifier. In this example, '1'.
[Table called 'SD' (or precisely VBRK-P in SAP)]
| SdId | Weight | SdDate | SdMonth | Batch |
|---|---|---|---|---|
| 1 | 1 | 6/2/2015 | Feb15 | 1 |
| 2 | -1 | 13/2/2015 | Feb15 | 1 |
| 3 | 1 | 13/2/2015 | Feb15 | 1 |
*Tokes for SD table is:
SdId = primary key, generated.
Weight = if it is invoice, +1, if reversed (such as credit note) -1.
SdDate = invoice issue date
SdMonth = dual value of MonthName(SdDate)
Batch = product
- FI and SD is many-to-many relation (many SdId has many FiId). but in this case 1 Batch has multiple SdId and FiID. User always selects SdMonth → (will lead to) set of Batch → sets of FdId, so I did not created any bridge table.
→ this point might be that I should create a table with group-by per SdMonth per Batch instead of current table construction.
I must produce such aggregation:
- In SD table, sum Weight per Batch Number per selected month. If it were non-zero, sum all historical Cost item in FI table per batch number.
- Besides, only select those batch, of which Revenue (i.e. Sum({<RC={Revenue}>} Amount)) is positive or negative.
Firstly I have created SdMonth as selector on UI.
I have produced such set analysis, but I am not sure if it is really fine from technical perspective.
If(Sum(Weight)<>0, //Sun if Weight is non-zero on the selected month in the UI
Sum({$<
RC={Cost}, //select only Cost
FiMonth={"<=SdMonth"}, //Cumulative only up to the selected month
Batch={"=Sum({<RC={Revenue}>}Amount)<>0"} //Select batch, of which revenue is non-zero
>}Amount)
For the Revenue,
Sum({<
RC={Revenue}, FiMonth=SdMonth>} Amount)
I would appreciate for your advices/feedback in advance for above set analysis.
* * * [Background] * * *
Such requirement emerged when we wished to explore FBL3N in SAP transaction per GL account. Typically sales is initiated in SD module, and re-posted to FI. SD and FI not necessarily match it, so we do not merge SD and FI table. Above, 'Cost/Revenue' is precisely BSEG-HKONT, where actual GL account is assigned. Weight in SD table is based on VBRK-FKART, the document type.
Cost must be cumulative amount, while revenue is generated in single month. To create explorative capability of gross profit, we ought to sum cost historically, while revenue should show only single month.
