Set Analysis: select sets based on specific sum()

atsushi_saijo — Tue, 17 Feb 2015 09:17:32 GMT

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.

Re: Set Analysis: select sets based on specific sum()

Tue, 17 Feb 2015 09:40:08 GMT

Hi Atsushi,

QlikView will connect your two tables by using the Batch field, because it is the only field in both tables that has the same name. I asume that performance will not be very good because your tables have a many to many relationship and you probably have a lot of data.

As you suggest, the aggregation on Batch, Weight and Month during Load might be a good idea.

regards,

Oliver

Re: Set Analysis: select sets based on specific sum()

atsushi_saijo — Fri, 20 Feb 2015 09:03:26 GMT

Thank you Oliver for your comment.

topic Set Analysis: select sets based on specific sum() in QlikView

Set Analysis: select sets based on specific sum()

Re: Set Analysis: select sets based on specific sum()

Re: Set Analysis: select sets based on specific sum()