Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
---|---|---|---|
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(%) |
---|---|---|---|
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:
Measures:
I appreciate your help.
Thank you.
Try with this expression
=Sum(Aggr(If(Sum(Earnings) > Range2, [Basic Tax], (Sum(Earnings) - Above(Range2))*[Excess(%)]), Name, (Range2, (NUMERIC))))
Try with this expression
=Sum(Aggr(If(Sum(Earnings) > Range2, [Basic Tax], (Sum(Earnings) - Above(Range2))*[Excess(%)]), Name, (Range2, (NUMERIC))))
HI Sunny,
I will try the example and download sample qvf.
I will update if this is what i want.
Thank you
Thank you so much Sunny.
This is exactly what I want.