
Re: Already perform division in script ?
Amit Saini Sep 1, 2016 5:13 AM (in response to Christina M�ller)Hi,
Try something like below:
load *
sum(sales) / sum(profit) as Value
Resident TableA
Thanks,
AS

Re: Already perform division in script ?
Henric Cronström Sep 1, 2016 5:20 AM (in response to Christina M�ller)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 ?
Andrew Walker Sep 1, 2016 5:24 AM (in response to Christina M�ller)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 ?
Christina M�ller Sep 1, 2016 5:36 AM (in response to Christina M�ller)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 ?
Andrew Walker Sep 1, 2016 6:08 AM (in response to Christina M�ller)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 ?
Christina M�ller Sep 2, 2016 7:44 AM (in response to Andrew Walker)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 ?
Andrew Walker Sep 2, 2016 12:01 PM (in response to Christina M�ller)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.
OR
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


