5 Replies Latest reply on Dec 11, 2018 7:00 AM by Norbert Maijoor

    Filter to get Sum for only a set of values

    Vinod Chilukuri

      Hi,

       

      My Data Expenses which has Expense Codes like A-12, G-7, C-1, G-2 etc and some of these expenses have SGST and CGST or IGST, All the expenses are segregated by Journal Vouchers.

       

      Refer the attached packaged work books, Actual File and Required File.

       

      Actual File is the way the report gets generated, where as the Required file is with unwanted rows are excluded from report. This is done manually on a small data set but not practically possible on a large data set i am working on.

       

      The requirement is for All Journal Vouchers, sum of Debit Amount is to be calculated where the Particulars - Split-1 has Only Expense Code, Expense Code along with Input-SGST/CGST, Expense Code along with Input-IGST. All Journal Vouchers with Only Input-SGST/CGST and Only Input-IGST are to be excluded.

       

      Please Help!

       

      Regards

       

      Vinod 

        • 1. Re: Filter to get Sum for only a set of values
          Cédric Tran

          Hello,

           

          You have two ways to do a calculation based on values:

          1. create a calculated measure with this type of formula: sum(if [split] = 'Input-CGST' then [Debit Amount] end)

          2. if there are more values to summate , create a set based on your dimension; create a calculated measure based on your Set; sum(if [set] then [debit amount] end )

           

          Cedric

          • 2. Re: Filter to get Sum for only a set of values
            Vinod Chilukuri

            Hi Cedric,

             

            Thanks for your response!

             

            May be i have not correctly mentioned what is required, hence the suggestion didn't work.

             

            Let me explain again, my data has more than 100000 rows and it keeps increasing by around 50 rows daily. I am looking for a solution to get - For Every [Vch No.] the [Debit Amount] is required where [Split] contains [Set1] and [Set2] or only [Set1] but exclude all [Vch No.] where [Split] contains on [Set2].

             

            [Set1] contains Expense Codes like A1, A2......A-1,A-2........N1, N1.1........ etc

            [Set2] contains Tax Codes --> GST, CGST, SGST, Input-CGST, Input-SGST and Input

             

            I am attaching another twbx file with a large data set.

             

            Regards

             

            Vinod

            • 3. Re: Filter to get Sum for only a set of values
              Norbert Maijoor

              Hi Vinod,

               

              Find my approach as reference below and stored attached workbook version 10.5 located in the original thread.

               

               

              I joined the files based on a 1:1 inner-join.

               

               

              1. D1. Expense Code Check: if contains([Particulars - Split 1],[Expense Code])=TRUE then [Expense Code] end

               

              2. D2. Tax Code Check : if contains([Particulars - Split 1],[Tax Code])=TRUE then [Tax Code] end

               

              3. D3. Tax/Expense Code: if isnull([D1. Expense Code Check])=TRUE then [D2. Tax Code Check ]

              elseif isnull([D2. Tax Code Check ])=TRUE then [D1. Expense Code Check]

              END

               

              4. D4. Tax/Expense Code (group)

               

              5. D5. Display:

              [D4. Tax/Expense Code (group)]='Set 2'

              and {fixed [Vch No.]:max([D4. Tax/Expense Code (group)])}={fixed [Vch No.]:min([D4. Tax/Expense Code (group)])}

               

              6. Drag the required objects to the indicated locations and set the following filters D3. Tax/Expense Code: Exclude NULL & D5. Display: False

               

              set.png

               

              Hope it helps,

               

              Regards,

              Norbert

              1 of 1 people found this helpful
              • 4. Re: Filter to get Sum for only a set of values
                Vinod Chilukuri

                Hi Norbert,

                 

                Thanks for detailed working!

                 

                Your working suggestion really helped me to solve the problem in a different way.

                 

                Thanks once again.

                 

                Regards

                 

                Vinod

                • 5. Re: Filter to get Sum for only a set of values
                  Norbert Maijoor

                  Hi Vinod,

                   

                  You are welcome. Please close the thread with a  "Helpfull" or "Correct Answer" so the community is aware and can focus on other challenges.

                  Upfront thanks a TON for your cooperation. Much appreciated:)

                   

                  Regards,

                  Norbert