- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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