I need to reproduce this type of logic in my script in order to calculate our summarized order margin amount. The below calculations are done on a unit price level. How can I reproduce this logic in my load script. The logic is based on a combination of sales currency and purchase currency. Can someone suggest how to do this?
This looks like a pretty open-ended problem. What specifically do you need help with?
Do you need help applying the WHEN logic? You could handle the WHENs as IFs within one load statement. You could handle the WHENs as WHENs and have multiple load statements concatenating data.
Do you need help looking up an exchange rate from a separate table? You appear to be calling a function of some sort, and don't provide the internals of the function, so I'm not sure I can help with that without more information.
Do you need help condensing this down? In QlikView, you can only refer to fields that have already been loaded. So if you want Duty to use Freight in its calculation, you either need to repeat the Freight expression, or use multiple loads, left joining each new field onto the existing data.
You might want to implement an exchange rate for each row, with a value of 1 when there's no currency difference. Then you could always divide by the Exchange rate at the end.
I wouldn't multiply your percentage by 100. QlikView can display 0.73 as 73% with no problem.
Basically, the code calculates profit margin on a unit level. I would like to create a chart/gauge to show total profit margin %. In order to this, I would need to sum up sales amount and cost amount, but both can vary based on Sales currency and purchase currency. Every field need for calculation is included in source file.including exchange rate, so no look up is necessary.
if sales currency and purchase currency = usd and usd, then logic 1 is used. jpy and jpy logic2 is used, and usd and jpy logic3 is used.
I guess to be more specific my question is how should using different price fileds based on currency.
Well, unless I'm missing something, the logic across all three is basically identical except for the use of C2_TPLCAB vs. C2_TPOCAB, at least if your exchange rate for equal currencies is loaded as 1. But we can handle it even if it isn't. If load speed isn't a problem, here's a multi-pass approach that keeps the calculations simple:
[Data]: LOAD recno() as ID ,if(C2_SCURAB='USD' and C2_PCURAB='USD',C2_TPLCAB,C2_TPOCAB) as C2_TPxCAB ,if(C2_SCURAB=C2_TPLCAB,1,ExchRate) as ExchRate ,C2_FCHPAB ,C2_DUTPAB ,C2_SPLCAB as SalesPrice ... ; LEFT JOIN LOAD ID, C2_TPxCAB / ExchRate as TPPrice RESIDENT ([Data]); DROP FIELD C2_TPxCAB; LEFT JOIN LOAD ID, TPPrice * C2_FCHPAB as Freight RESIDENT ([Data]); DROP FIELD C2_FCHPAB; LEFT JOIN LOAD ID, (TPPrice + Freight) * C2_DUTPAB as Duty RESIDENT ([Data]); DROP FIELD C2_DUTPAB; LEFT JOIN LOAD ID, TPPrice + Freight + Duty as Cost RESIDENT ([Data]); DROP FIELD ID;
Profit margin can't be calculated in the load because you have to sum, THEN divide, and display as percent to avoid multiplying by 100. So that become a chart expression like this:
1 - Cost / SalesPrice
If load speed IS a problem, you probably won't want to make multiple passes. You may want to compare the speed of a single pass approach. Since QlikView won't recognize calculated fields until the load is complete, it's an algeabra problem to expand all of the expressions back to the original variables. Assuming I did my algeabra right, something like this:
LOAD if(C2_SCURAB='USD' and C2_PCURAB='USD',C2_TPLCAB,C2_TPOCAB) /if(C2_SCURAB=C2_TPLCAB,1,ExchRate) as TPPrice ,if(C2_SCURAB='USD' and C2_PCURAB='USD',C2_TPLCAB,C2_TPOCAB) * C2_FCHPAB /if(C2_SCURAB=C2_TPLCAB,1,ExchRate) as Freight ,if(C2_SCURAB='USD' and C2_PCURAB='USD',C2_TPLCAB,C2_TPOCAB) * (1 + C2_FCHPAB) * C2_DUTPAB /if(C2_SCURAB=C2_TPLCAB,1,ExchRate) as Duty ,if(C2_SCURAB='USD' and C2_PCURAB='USD',C2_TPLCAB,C2_TPOCAB) * (1 + C2_FCHPAB) * (1 + C2_DUTPAB) /if(C2_SCURAB=C2_TPLCAB,1,ExchRate) as Cost ,C2_SPLCAB as SalesPrice ... ;
I am looking for some help, and I think you are the best person to ask. I have kind of similar problem. I am trying to do full margin analysis, with a possibility to see margin to each product, product group, customer and customer group. In my script it looks like that:
/*****Main****/ Main: LOAD ID, faknr, fakdat as CalendarDate, artcode, if(left(oms45,10)='LOEMPIAVEL','99',if(left(artcode,5)='14020','98',left(artcode,2))) as ProductgroepNummer, oms45 as ProductDescription, afl_week, esr_aantal, -----> number of items korting, prijs_n, prijs83, vvp, pr_bedr, ordernr, verzdebnr as Klantnummer, bdr_ev_ed_val as InvoiceAmount, bdr_d_ev_val, bdr_inv_d_val FROM [Amboina data needed.xlsx] (ooxml, embedded labels, table is Sheet1);
/*****Customer****/ Customer: LOAD Klantnummer, Klantnaam, [New client group] as KlantGroep FROM [Customer list final, vs HJ.xls] (biff, embedded labels, table is Blad1$);
/*****Product****/ Product: LOAD ProductgroepNummer, Productgroep FROM [091215 Amboina Productgroepen.xlsx] (ooxml, embedded labels, table is Sheet1); Margin122008: LOAD Artikel as artcode, Artikelomschrijving, Artikelgroep, Omschrijving, Status, Eenheid, Kostprijs, FROM [090603 Voorraad per 31_12_2008, final version HJ.xlsx] (ooxml, embedded labels, table is Blad1);
By usage of expression in chart i got margin for each product (I used average price of each item = 1- Kostprijs/ avg(prijs83), what is also kind of incorrect, as we sell to customers with different prices, so if I understand mathematics correctly, the result is not the same). But I have no idea how I could check the margin for each customer etc.
That's close, but we actually need to use the number of ITEMS, not the number of invoices. The number of items on an invoice is specified by field esr_aantal. So you want a weighted average price by esr_aantal. That would be sum(prijs83*esr_aantal)/sum(esr_aantal). Plugging THAT in:
Another way to have come at this is to recognize that we want the TOTAL cost, which would be Kostprijs*sum(esr_aantal). And that we want the TOTAL price, which would be sum(prijs83*esr_aantal). Both ways of thinking end up with the same expression, of course. So repeating myself, and assuming I understood your question, the answer is:
Firstly thank you very much for spending time on that! definitely you understood the case correctly. So right now thanks to final formula I found out more proper item margin. Unfortunately still the case how to find out customer margin or product group margin is mission impossible for me. So, putting customer name as dimension and 1 - Kostprijs*sum(esr_aantal)/sum(prijs83*esr_aantal)as an expression in a chart doesn't work:( It's my first margin analysis in my whole life, so I am really appreciating your help!
I'd have expected the expression to work fine when breaking down by customer. It should limit the number of rows from the Main table, and therefore restrict sum(esr_aantal) and sum(prijs83*esr_aantal) to just the matching rows, and therefore give you a margin for just that customer. So I'm not sure what's going wrong. What are you seeing? Some wrong answer? A null answer? Maybe post a quick example with inline loads demonstrating the problem?
I hoped also it would work:-) If I use Customer Group as dimension there is no data displayed. When I use Customer as a dimension I have a result of 1 for three chosen customers (I didnt get it yet how QV restricted that)> Right now I am trying to upload the file, but without success, is the size restricted?
