topic Re: How to do % calculation in Qlikview in QlikView

How to do % calculation in Qlikview

Mon, 13 Jun 2016 11:02:15 GMT

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

Re: How to do % calculation in Qlikview

florentina_doga — Mon, 13 Jun 2016 11:08:01 GMT

what is tax rate for

ST-60-14S

ST-70-10

ST-75-10

Re: How to do % calculation in Qlikview

florentina_doga — Mon, 13 Jun 2016 11:16:29 GMT

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.

Re: How to do % calculation in Qlikview

Anonymous — Mon, 13 Jun 2016 11:58:40 GMT

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!

Re: How to do % calculation in Qlikview

Mon, 13 Jun 2016 12:26:33 GMT

Hi,

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

Regards

Re: How to do % calculation in Qlikview

Anonymous — Mon, 13 Jun 2016 13:12:27 GMT

Hi Nisha,

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

Regards!

Re: How to do % calculation in Qlikview

Tue, 14 Jun 2016 04:56:37 GMT

Hi,

Tax Rate for

ST-60-14s - 14.50%

ST-70-10 - 10%

ST-75-10- 10%

Needs to take for tax calculation.

Regards

Re: How to do % calculation in Qlikview

Tue, 14 Jun 2016 05:06:43 GMT

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

Re: How to do % calculation in Qlikview

Anonymous — Tue, 14 Jun 2016 06:27:13 GMT

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