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.
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.
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 ^^
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.
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!!
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.