Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want Qlikview syntax for 4,5 &6 row calculation in qlikview.
Please find the attached excel sheet and let me know how we can code tax calculation in qlikview.
Please refer to column result after tax calculation.
Please suggest...How we can do such type of calculation in Qlikview
Regards
what is tax rate for
ST-60-14S |
ST-70-10 |
ST-75-10 |
try this
load *,
[TAX RATE]*[Tax Amount] as [Result aafterTax Calculation1];
load * inline [TAX CODE, TAX RATE, Tax Amount, Result aafterTax Calculation
ST-0-10, 10%, 10000, 1000
ST-0-14, 14%, 50000, 7000
ST-60-14S, Tax amount of 40% ..whatever 40% will come take tax on it as 14.50%, 20000, 1160
ST-70-10, Tax amount of 30% ..whatever 30% will come take tax on it as 10%, 25000, 750
ST-75-10, Tax amount of 25% of it ..whatever 25% will come take tax on it as 10%, 15000, 375];
but for ST-60-14S,ST-70-10, ST-75-10 not work. tax rate is not numeric value.
Hi nicha.nan:
Try this code:
LOAD *,
([Tax Amount]*
If(IsNull([TAX RATE3]),1,[TAX RATE3]))*If(IsNull([TAX RATE]),[TAX RATE2],[TAX RATE]) as Result;
LOAD [TAX CODE],
Num([TAX RATE],'#.##0,00 %') AS [TAX RATE],
Num(Num#(IF([TAX RATE]>0 and [TAX RATE] < 100, Num(Num#(PurgeChar([TAX RATE],'%'),'##.00','.',',')/100,'#.##0,00 %'),
Purgechar(SubField(Upper([TAX RATE]),'..',1),Upper('abcdefghijklmnopqrstuvwxyz %'))),'##.00','.',',')/100,'#.##0,00 %') as [TAX RATE2],
Num(Num#(Purgechar(Subfield(SubField(Upper([TAX RATE]),'..',2),'AS',2),Upper('abcdefghijklmnopqrstuvwxyz %')),'##.00','.',',')/100,'#.##0,00 %') as [TAX RATE3],
[Tax Amount],
[Result aafterTax Calculation]
FROM
Comm.xlsx
(ooxml, embedded labels, table is [1463051585931]);
Regards!
Hi,
Result aafterTax Calculation is not the column; i have just shown you the calculation for 4,5 & 6 row Tax % Calculation.
Regards
Hi Nisha,
I know, I've created a column called Result to compare with your column.
Regards!
Hi,
Tax Rate for
ST-60-14s - 14.50%
ST-70-10 - 10%
ST-75-10- 10%
Needs to take for tax calculation.
Regards
Hi,
I cant see your your code or column .
Please let me know how we can calculate the tax for 4,5 & 6 row in attached Excel sheet.
Tax Rate for
4. ST-60-14s - 14.50%
5. ST-70-10 - 10%
6. ST-75-10- 10%
Tax needs to calculate as :
For eg : if ST-60-14s has 20000tax amount we have to calculate 20000*40% = 8000 ; Now on 8000 we have to take 14.50%= 1160 ; 1160 amount should display in tax amount.
it is like 100-60=40%
Please let me know how we can calculate such requirement in qlikview.
Hope you understood my issue.
Regards
Hi nisha,
LOAD *,
/*In this line the final result is calculated. In the first part of the multiplication, if TAX RATE3 is null, then we take the entire amount, but multiplied by the percentage corresponding to TAX RATE3.
Then multiply by the percentage of TAX RATE2 if TAX RATE is null, but is multiplied by TAX RATE.*/
([Tax Amount]* If(IsNull([TAX RATE3]),1,[TAX RATE3]))*If(IsNull([TAX RATE]),[TAX RATE2],[TAX RATE]) as Result;
LOAD [TAX CODE],
//Here you have the Tax of first row in number, called TAX RATE
Num([TAX RATE],'#.##0,00 %') AS [TAX RATE],
//Here you have the first percentage of the lines with text, called TAX RATE2
Num(Num#(IF([TAX RATE]>0 and [TAX RATE] < 100, Num(Num#(PurgeChar([TAX RATE],'%'),'##.00','.',',')/100,'#.##0,00 %'),
Purgechar(SubField(Upper([TAX RATE]),'..',1),Upper('abcdefghijklmnopqrstuvwxyz %'))),'##.00','.',',')/100,'#.##0,00 %') as [TAX RATE2],
//Here you have the second percentage of lines with text, called TAX RATE3
Num(Num#(Purgechar(Subfield(SubField(Upper([TAX RATE]),'..',2),'AS',2),Upper('abcdefghijklmnopqrstuvwxyz %')),'##.00','.',',')/100,'#.##0,00 %') as [TAX RATE3],
[Tax Amount],
[Result aafterTax Calculation]
FROM
Comm.xlsx
(ooxml, embedded labels, table is [1463051585931]);
Regards!!