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

    How to calculate tax based on tax table

    Vannie Ariola

      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.