topic Re: Counter account analysis in QlikView

Counter account analysis

Mon, 27 Mar 2017 12:12:15 GMT

Hello comminity!

A bookkeeping journal with n entries and m entrylines should be filtered by the linked account so that every entry with every line is included just in case one line of each entry fits to the choosen/filtered account or set of accounts.

Example:

Journal File:

Journal entry | Line# | Account# | Value

1 | 1 | 4711 | +50

1 | 2 | 4712 | -50

2 | 3 | 4711 | +100

2 | 4 | 4713 | -30

2 | 5 | 4714 | -70

3 | 6 | 4712 | +99

3 | 7 | 4714 | -99

Selection via user-click Account = 4711

Result:

1 | 1 | 4711 | +50

1 | 2 | 4712 | -50

2 | 3 | 4711 | +100

2 | 4 | 4713 | -30

2 | 5 | 4714 | -70

3 | 6 | 4712 | +99

3 | 7 | 4714 | -99

Sum (4711) = +150, Sum (4712) = -50, Sum (4713) = -30, Sum (4714) = -70

Selection via user-click Account = 4712

Result:

1 | 1 | 4711 | +50

1 | 2 | 4712 | -50

2 | 3 | 4711 | +100

2 | 4 | 4713 | -30

2 | 5 | 4714 | -70

3 | 6 | 4712 | +99

3 | 7 | 4714 | -99

Sum (4711) = +50, Sum (4712) = +49, Sum (4714) = -99

Selection via user-click Account = 4711,4714

Result:

1 | 1 | 4711 | +50

1 | 2 | 4712 | -50

2 | 3 | 4711 | +100

2 | 4 | 4713 | -30

2 | 5 | 4714 | -70

3 | 6 | 4712 | +99

3 | 7 | 4714 | -99

Sum (4711) = +50, Sum (4712) = +49, Sum (4714) = -99

Is there a simple method/dynamic function to filter the not relevant entries out? Please give advice. Thank you very much in advance from Hamburg, Germany.

Kind regards,

Wieland

Re: Counter account analysis

Anil_Babu_Samineni — Tue, 28 Mar 2017 02:52:57 GMT

I am understanding the logic behind, But i am not following the 4712. Does you mean this for 4712

Result:

1 | 1 | 4711 | +50

1 | 2 | 4712 | -50

2 | 3 | 4711 | +100

2 | 4 | 4713 | -30

2 | 5 | 4714 | -70

3 | 6 | 4712 | +99

3 | 7 | 4714 | -99

Result finally this? the serial is 4711, 4713, 4714 --> 100, -30, -70 or same thing you want? Can you describe more

Re: Counter account analysis

marcus_sommer — Tue, 28 Mar 2017 07:04:31 GMT

I think there are possibilities to solve this selection-requirement within the script maybe by The As-Of Table but it might be easier to force the expression with set analysis to your wanted results. It could be done with something like:

sum({1< [Journal entry] = p([Journal entry])>} Value)

- Marcus

Re: Counter account analysis

Tue, 28 Mar 2017 09:53:36 GMT

Hello Anil,

please have a second look into my first post. From my standpoint there is no integrityproblem in my examples.Thank you very much for your reply.

KInd regards,

Wieland

Re: Counter account analysis

Tue, 28 Mar 2017 10:06:01 GMT

Hello Marcus,

I find a workaround for that matter:

Select the intresting account numbers via clicking
Select the Journal entry numbers via the search function (... selectable values)
Deselect the interesting account numbers

I am not sure if your function will do the same. Unfortunately I have an importproblem with your testfile to check it out. I will try your guess after fixing the compatibility problem. Thank you very much for your fast and high quality answer.

Kind regards,

Wieland