topic Re: Iterative calculation in skript (Qlikview or Qliksense) like Excel in App Development

Iterative calculation in skript (Qlikview or Qliksense) like Excel

hbuchetmann — Mon, 26 Sep 2022 10:34:03 GMT

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

Re: Iterative calculation in skript (Qlikview or Qliksense) like Excel

canerkan — Mon, 26 Sep 2022 11:24:10 GMT

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

Re: Iterative calculation in skript (Qlikview or Qliksense) like Excel

hbuchetmann — Mon, 26 Sep 2022 12:35:09 GMT

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.

Re: Iterative calculation in skript (Qlikview or Qliksense) like Excel

MarcoWedel — Mon, 26 Sep 2022 20:59:58 GMT

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