topic Re: Already perform division in script ? in QlikView

Already perform division in script ?

Anonymous — Thu, 01 Sep 2016 09:00:09 GMT

Hallo QlikView Community,

I want to calculate the sales margin by dividing two different numbers.

Therefore I want to define a new field. Something like this:

sales margin = sum(sales) / sum(profit)

But because I have many different tables, I want to do it already in the script. Furthermore the answer should be in percent.

Can anybody help me, because I have no Idea how to do it?

Thank you!

Re: Already perform division in script ?

amit_saini — Thu, 01 Sep 2016 09:13:41 GMT

Hi,

Try something like below:

load *

sum(sales) / sum(profit) as Value

Resident TableA

Thanks,

Re: Already perform division in script ?

hic — Thu, 01 Sep 2016 09:20:47 GMT

I don't think you really want to do this.

If you define this ratio in the script, like amit.saini‌ suggests, you will get number that looks OK (if you use a GROUP BY in the load). However, this is a static number that does not change when you make selections. But if you instead want a dynamic number that respects your selection, you should use sum(sales) / sum(profit) as a measure in an object.

HIC

Re: Already perform division in script ?

effinty2112 — Thu, 01 Sep 2016 09:22:06 GMT

Hi Christina,

I think the best idea would be to calculate such ratios in the user interface (BTW this is the ratio you may need: sum(profit)/sum(sales) .

If you calculate them at record level (in the script) then you can't do much with them at a higher level. If you work out a margin at an order line level aggregating the values of these ratios is meaningless.

If you create a chart which sums your sales and your profit then that is where you should put your sales margin calculation.

Kind regards

Andrew

Re: Already perform division in script ?

Anonymous — Thu, 01 Sep 2016 09:36:18 GMT

Thank you for your quick answer!

So the Problem is, that I have two different dimensions( please don't be disturb by the German !)

On the one hand side I want to have the different Quartals and Budget and stuff like this . (column)

But on the other side I also want to have different Values for order, sales, margin, profit etc.! (line)

I already managed to do this with maaany if formulas, but if I then have to calculate something too, it is just getting to complicated and long...

So if there is a formula like VLOOKUP in excel, that would also help me!

For example: If the Title is 'sales' then pick the sales value

Okay I hope somebody understands my problem ^^

Re: Already perform division in script ?

effinty2112 — Thu, 01 Sep 2016 10:08:19 GMT

Hi Christina,

I can see a couple of different ways to approach this. If I understand your picture correctly you have four dimensions of which TGL_CS is the most interesting, this is the one that goes horizontally.

Does each value of TGL_CS correspond to a different calculation you want in the column? If so we can either:

1/ Remove this dimension and replace with 18 expressions.

2/ Keep the dimension and have one expression that will be a large Pick(Match( ...)) expression that will perform a different calculation for each value of TGL_CS.

Please check that I have understood this and then if you tell me the expression for [IST 2015 Monat] and [IST 2015 Monat ]I'll try to show what I mean for these two calculations then we hopefully add the others afterwards.

Kind regards

Andrew

Re: Already perform division in script ?

Anonymous — Fri, 02 Sep 2016 11:44:53 GMT

Thank you for your answer!

The interesting point is not tgl but Titel!

One definition of one formula is now:

=Sum(if(Titel='Auftragseingang' and cat='VJ MS', [AE_Gesamt],
if(Titel='Umsatz' and cat='VJ MS',[Umsatz_Gesamt],
if(Titel='Auftragsbestand' and cat='VJ MS',[AB Gesamt],
if(Titel='Vertriebsspanne' and cat='VJ MS', VSP_Gesamt,
if(Titel='VSP in %' and cat='VJ MS', VSP_Gesamt/Umsatz_Gesamt,
if(Titel='Suko op/nop' and cat='VJ MS', [Sonstige Umsatzkosten (op/nop)],
if(Titel='Bruttoergebnis' and cat='VJ MS', [Bruttoergebnis absolut],
if(Titel='Bruttoergebnis in %' and cat='VJ MS', [Bruttoergebnis absolut]/Umsatz_Gesamt,
if(Titel='Vertriebskosten' and cat='VJ MS', Vertriebskosten,
if(Titel='Vertriebskosten in %' and cat='VJ MS', Vertriebskosten/Umsatz_Gesamt,
if(Titel='Vertriebskosten_op' and cat='VJ MS', Vertriebskosten_Op,
if(Titel='Vertriebskosten_N_op' and cat='VJ MS', Vertriebskosten_N_Op,
if(Titel='Sonstige Kosten' and cat='VJ MS',sonstige_Kosten,
if(Titel='Ergebnis' and cat='VJ MS', Ergebnis,
if(Titel='Ergebnis in %' and cat='VJ MS', Ergebnis/Umsatz_Gesamt))))))))))))))))

So it is quite huge! And some of them are even more complicated (the one with the %) Maybe I can do some matching, like you offered?

And than it would be easier to calculate in the formula?

Thank you very much!!

Kind regards

Christina

Re: Already perform division in script ?

effinty2112 — Fri, 02 Sep 2016 16:01:06 GMT

Hi Chistina,

That is a horribly large expression and you say that it is only one of them! I think your best friend here will be the Variable Overview found in the Settings Menu.

I would take out the and cat='VJ MS' parts and save this in a variable and call it something like vIfCatIsVJ_MS. This variable could then be referred to when cat='VJ MS' is true.

I don't know how familiar you are with the use of QlikView variables but when you've saved the definition of this variable you can then refer to the variable like so: $(vIfCatIsVJ_MS), called a dollar sign expansion (DSE).

You can create a different variable for each value of Cat and refer to each one by DSE and put them all on a straight table as separate expressions.

You can refer to a variable inside another .If Cat takes on, let's say three values (call them 'VJ_MS', 'Neil, 'Alex') you can have an expression like:

vWhatever = Pick(Match(Cat, 'VJ_MS', 'Neil, 'Alex'), $(vIfCatIsVJ_MS),$(vIfCatIsNeil),$(vIfCatIsAlex))

You can write the above as a nested if statement if you prefer of course. This variable vWhatever takes care of an array of possibilities, a range of values of Titel contained in your original expression and a range of values for Cat. Perhaps such an approach may help you to get a single expression you can use in a pivot table.

It's not possible to give firm advice when I know so little about your application but I hope this points you in a direction that will get you nearer your goal and not lead you into a blind alley.

Tschüss!

Andrew