How to do % calculation in Qlikview

[#1]

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

[florentina_doga]

what is tax rate for

ST-60-14S

ST-70-10

ST-75-10

[florentina_doga]

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.

[Anonymous]

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!

[#5]

Hi,

Result aafterTax Calculation  is not the column; i have just shown you the calculation  for 4,5 & 6 row Tax % Calculation.

Regards

[Anonymous]

Hi Nisha,

I know, I've created a column called Result to compare with your column.

Regards!

[#7]

Hi,

Tax Rate for

ST-60-14s - 14.50%

ST-70-10 - 10%

ST-75-10- 10%

Needs to take for tax calculation.

Regards

[#8]

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

[Anonymous]

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