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

# Filter to get Sum for only a set of values

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.

Regards

Vinod

• ###### 1. Re: Filter to get Sum for only a set of values

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

Hi Cedric,

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

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

Hope it helps,

Regards,

Norbert

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

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

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