Iterative calculation in skript (Qlikview or Qliksense) like Excel

[hbuchetmann]

Hello together,

i am looking for a solution in Qlikview or QLiksense for the following problem.

I want to calculate a Selling price for a marketplace. 

My input data (see testdata.xlsx) ist:

Name = name of the product

price = price we pay for the product

margin = the margin we want after all costs are substracted from teh sellingprice

tax19% = the tax on the product when it got sold on the marketplace

fee13% = the fee we have to pay to the marketplace for selling the product

I want to calculate the column sellprice in QLikview / qliksense, so that

sellprice - fee13% - tax19% - price = margin

My problem: tax19% and fee13% are calculated from the sellprice.

In Excel, there is a solution when to activate iterative calculation unter option - formula (without you get the circular reference error).

Is there a solution for this in qlikview / qliksense for calculating sellprice in the skript?

Thanks in advance for your help.

Hermann

[canerkan]

Hi HBuchetmann,

you could try this line in your loadscript:

sellprice - (sellprice * 0.19) - (sellprice * 0.13) - price as Margin

Let me know if it helped.

Regards,

Can

[hbuchetmann]

Hello Can,

i try to calculate sellprice in the skript, not the margin.

In the testdata sellprice is calculated with excel just to demonstrate how it works in excel.

[MarcoWedel]

There is no need for numerical iterations if you just solve those equations for sellprice:

table1:
LOAD *,
     sellprice/119*19			as tax19%,
     sellprice*0.13				as fee13%;
LOAD Name, 
     price, 
     margin, 
     (price+margin)/0.710336	as sellprice	// 100%-13%-19%/119% = 0.710336
FROM [https://community.qlik.com/cyjdu72974/attachments/cyjdu72974/qlik-sense-app-development/81340/1/testdata.xlsx] (ooxml, embedded labels, table is Tabelle1);

 hope this helps

Marco