How to calculate tax based on tax table

[vannieariola]

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

Date	Name	Tax ID	Earnings
1/31/2017	Vannie	000001	30000.00
2/28/2017	Vannie	000001	30000.00
3/31/2017	Vannie	000001	30000.00
4/30/2017	Vannie	000001	30000.00

Total is 120,000

data2 contains tax table with columns:

• Range1
• Range2
• Basic Tax
• Excess (%)

Range1	Range2	Basic Tax	Excess(%)
0	10,000	0	0%
10,001	50,000	5,0000	10%
50,001	100,000	10,000	15%
100,001	150,000	15,000	20%

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(%)

I appreciate your help.

Thank you.

[sunny_talwar · Accepted solution]

Try with this expression

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

[vannieariola]

HI Sunny,

I will try the example and download sample qvf.

I will update if this is what i want.

Thank you

[vannieariola]

Thank you so much Sunny.

This is exactly what I want.