4 Replies Latest reply: Mar 28, 2017 5:53 AM by Wieland Kirch RSS

    Counter account analysis

    Wieland Kirch

      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

          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

            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
                Wieland Kirch

                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