Counter account analysis

[#1]

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

[Anil_Babu_Samineni]

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

[marcus_sommer · Accepted solution]

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

[#4]

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

[#5]

Hello Marcus,

I find a workaround for that matter:

1. Select the intresting account numbers via clicking
2. Select the Journal entry numbers via the search function (... selectable values)
3. 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