Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vannieariola
New Contributor

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

I appreciate your help.

Thank you.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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

3 Replies
MVP
MVP

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

vannieariola
New Contributor

Re: How to calculate tax based on tax table

HI Sunny,

I will try the example and download sample qvf.

I will update if this is what i want.

Thank you

vannieariola
New Contributor

Re: How to calculate tax based on tax table

Thank you so much Sunny.

This is exactly what I want.