3 Replies Latest reply: Dec 25, 2017 12:55 AM by Vannie Ariola

# How to calculate tax based on tax table

Good day experts!

I am new to qlik sense and I want to know how calculate tax based on tax table. I want do it in pivot chart.

This is the senarion, data1 contain fact data with columns:

• Date
• Name
• Tax ID
• Earnings
DateNameTax IDEarnings

1/31/2017

Vannie00000130000.00
2/28/2017Vannie00000130000.00
3/31/2017Vannie00000130000.00
4/30/2017Vannie00000130000.00

Total is 120,000

data2 contains tax table with columns:

• Range1
• Range2
• Basic Tax
• Excess (%)
Range1Range2Basic TaxExcess(%)
010,00000%
10,00150,0005,000010%
50,001100,00010,00015%
100,001150,00015,00020%

I will load the two tables in qlik sense then compute for the tax.

In my sheet using pivot table will create a

dimension:

1. Name
2. Tax ID

Measures:

1. Sum(earnings)
2. Basic Tax ( how will get the value of this = 15,000)
3. Excess (how will get the value of this = (total earnings of 120,000 - Range1) x 20% Excess(%)

Thank you.

• ###### Re: How to calculate tax based on tax table

Try with this expression

=Sum(Aggr(If(Sum(Earnings) > Range2, [Basic Tax], (Sum(Earnings) - Above(Range2))*[Excess(%)]), Name, (Range2, (NUMERIC))))

• ###### Re: How to calculate tax based on tax table

HI Sunny,

I will update if this is what i want.

Thank you

• ###### Re: How to calculate tax based on tax table

Thank you so much Sunny.

This is exactly what I want.