topic If statement in QlikView

If statement

karensmith — Sat, 08 Aug 2009 02:06:19 GMT

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?

===>

USD & USD ......

When C2_SCURAB = 'USD' And C2_PCURAB = 'USD'

Eval Freight = C2_TPLCAB * C2_FCHPAB

Eval Duty = (C2_TPLCAB + Freight) * C2_DUTPAB

Eval Cost = C2_TPLCAB + Freight + Duty

Eval PMargin=((C2_SPLCAB-Cost)/C2_SPLCAB)*100

====>

JPY & JPY ......

When C2_SCURAB = 'JPY' And C2_PCURAB = 'JPY'

Eval Freight = C2_TPOCAB * C2_FCHPAB

Eval Duty = (C2_TPOCAB + Freight) * C2_DUTPAB

Eval Cost = C2_TPOCAB + Freight + Duty

Eval PMargin=((C2_SPOCAB-Cost)/C2_SPOCAB)*100

====>

USD & JPY ......

When C2_SCURAB = 'USD' And C2_PCURAB = 'JPY'

Eval SMJ1DS = RtvExchRate(C2_CUSTAB:

C2_MURAAB:

C2_REELAB:

C2_CNORAB)

Eval TP_Pro = C2_TPOCAB / ExchRate

Eval Freight = TP_Pro * C2_FCHPAB

Eval Duty = (TP_Pro + Freight) * C2_DUTPAB

Eval Cost = TP_Pro + Freight + Duty

Eval PMargin=((C2_SPLCAB-Cost)/C2_SPLCAB)*100

If statement

johnw — Sat, 08 Aug 2009 03:02:35 GMT

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.

If statement

karensmith — Mon, 10 Aug 2009 04:55:31 GMT

Thanks for your response John.

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.

Calculating cost = (tp price +freight + duty)

margin=salesprice-(tp price+freight +duty)/salesprice

Yes, I need assistance with load the when statements as if statments in Qlikview, or what is the best logic for QV script.

I need to load values based on local or orginal sales price and cost based on local or orignal tp price based on currency.

If statement

johnw — Tue, 11 Aug 2009 01:07:30 GMT

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
...
;

If statement

Wed, 10 Feb 2010 17:18:00 GMT

Dear John,

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.

I would really appreciate your help!

Thank you in advance,

Beata

If statement

johnw — Wed, 10 Feb 2010 19:16:08 GMT

OK, let me see if I understand.

"Main" is a table of invoices from you to your customers.
"esr_aantal" is the number of items on a particular invoice.
"prijs83" is the price for a single item. Different customers may pay a different price.
"Kostprijs" is the cost of the item. An item has the same cost, regardless of customer.

So your profit margin for ONE item is (prijs83 - Kostprijs)/prijs83, or more simply, 1 - Kostprijs/prijs83.

Your current calculation for ALL items is:

1 - Kostprijs/avg(prijs83)
= 1 - Kostprijs/(sum(prijs83)/numberofinvoices)
= 1 - Kostprijs*numberofinvoices/sum(prijs83)

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:

1 - Kostprijs/(sum(prijs83*esr_aantal)/sum(esr_aantal))
= 1 - Kostprijs*sum(esr_aantal)/sum(prijs83*esr_aantal)

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:

1 - Kostprijs*sum(esr_aantal)/sum(prijs83*esr_aantal)

If statement

Wed, 10 Feb 2010 20:51:17 GMT

Dear John,

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!

greetings from The Netherlands,

Beata

If statement

johnw — Wed, 10 Feb 2010 21:03:30 GMT

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?

If statement

Wed, 10 Feb 2010 21:52:24 GMT

Dear John,

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?

Thank you in advance,

Beata

If statement

Wed, 10 Feb 2010 21:54:51 GMT

Dear John,

Ok, I uploaded it into my files! Please let me know if you see it:)

Greetings,

Beata

If statement

johnw — Wed, 10 Feb 2010 21:56:09 GMT

I'm fairly sure the size is restricted, plus sometimes it simply doesn't work. I'd make the simplest possible sample file using inline data that still demonstrates the problem. If you find you can't make a sample file that demonstrates the problem, then you already have it solved, and just need to figure out how to apply it. 🙂

If statement

Wed, 10 Feb 2010 22:09:24 GMT

yes, it seems it doesn't work (I mean to post it here). Anyway it's uploaded in my profile files, and if you would be so kind, please take a look there! If I would solve this margin analysis, I suppose my boss would love me:) and you too:)

Greetings,

Beata

If statement

johnw — Wed, 10 Feb 2010 22:25:52 GMT

Ah, OK, I see the problem. My mistake. We can't just use Kostprijs that way. Using it that way assumes that everything in the whole data set costs the same amount, which isn't the case. So we need to aggregate costs, not just multiply one cost by the total number of items.

"artcode" identifies the type of item, right?

Frankly, your best option may be to left join the Margin table onto your Main table by artcode, and use this expression:

1 - sum(Kostprijs*esr_aantal)/sum(prijs83*esr_aantal)

QlikView's data compression should take care of the possible down sides. But sticking with a chart solution, I think this:

1 - sum(aggr(Kostprijs*sum(prijs83),artcode))/sum(prijs83*esr_aantal)

At least I hope that's right. It produces some numbers, anyway. Not sure if they're the right numbers.