Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
hbuchetmann
Contributor III
Contributor III

Iterative calculation in skript (Qlikview or Qliksense) like Excel

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

 

 

Labels (1)
3 Replies
canerkan
Partner - Creator II
Partner - Creator II

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
Contributor III
Contributor III
Author

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:

MarcoWedel_0-1664225980173.png

 

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