
Re: Too many formulas, HELP
Anil Babu Samineni Sep 16, 2016 12:12 PM (in response to Christina Müller)Would you be able to provide sample

Re: Too many formulas, HELP
Sunny Talwar Sep 16, 2016 12:21 PM (in response to Christina Müller)I agree with Anil, I think we would need to look at a sample before we can propose anything

Re: Too many formulas, HELP
Christina Müller Sep 19, 2016 4:39 AM (in response to Christina Müller)Sum(num(if(cat='Last years numbers (VJ MS)'and Titel='profit in %' ,total_profit/sales),'#.##0%'))
Does that help ? In the same forumla I also want to see profit in normal numbers,
The expression would be then:
sum(if(Titel='Profit' and cat='Last years numbers (VJ MS)', total_profit))
Thank you!

Re: Too many formulas, HELP
Marcus Sommer Sep 20, 2016 2:10 AM (in response to Christina Müller)Did you try the suggestion from Peter or me?
 Marcus


Re: Too many formulas, HELP
Or Shoham Sep 19, 2016 4:55 AM (in response to Christina Müller)This is indeed a rather frustrating problem with P&L and similar reports.
My recommended approach is to create a mapping file (I use Excel) with the names of the rows you want in the dimension and the actual data they should have, e.g.
PNL_NUM PNL_NAME CATEGORY PNL_STYLE PNL_ISPERCENT 10 Total Sales Sales 0 0 20 Total Cogs Cogs 0 0 25 Gross Profit Sales 1 0 25 Gross Profit Cogs 1 0 29 % GP Sales 2 1 29 % GP Cogs 2 1 Note the addition of a NUM field (to make sure the rows are in the right oder), STYLE (for bolding / backgrounds), and ISPERCENT (I use this to determine whether a row is a sum or a percentofsales value). CATEGORY is the actual data that should be associated with my synthetic (PNL_) rows.
Your formula would then be something along the lines of (I'm freehand writing this, so it may not be entirely accurate):
If(PNL_ISPERCENT=0,Num(Sum(VALUE),'#,##0'),
Num( Sum(VALUE) / Sum({< PNL_NAME = {"Total Sales"} >} Total VALUE),'#,##0.0%'))
The Num() is necessary to format the expression as a percent or a number depending on the line type. The first half of the formula deals with regular sum values while the latter deals with percent values (sum of the current row  which may contain multiple categories  divided by total sales in my example, but your situation may differ).
Unfortunately  and this is where it starts getting ugly most of the time  these reports tend to have a large number of expressions (in your example you're showing 17 of them), and each requires additional set analysis to get the correct data  so you'll have to adapt the base formula for each expression.
I hope this helps  I am not able to include my sample file as scrambling the data without breaking the actual example would be very timeconsuming.

Re: Too many formulas, HELP
Marcus Sommer Sep 19, 2016 7:12 AM (in response to Christina Müller)This couldn't work then you concat an expression with an OR and another expression which led to an error. I suggest to change the expression in this way:
if(cat = 'VJ MS',
pick(match(Title, 'Umsatz', 'Auftragsbestand', ....),
num(sum([Umsatz_Gesamt]), '#.#'),
num(sum([AB Gesamt]), '#.##0,0%'), // it's the wrong format for this expression  here only as example
....))
 Marcus

Re: Too many formulas, HELP
Peter Cammaert Sep 19, 2016 7:31 AM (in response to Christina Müller)To me it seems that your formula is broken. Check these two initial anomalies:
 Your last IF() function in the first Sum() has space for a third parameter, but the parameter itself is missing. That makes the expression illegal.
 A Sum() function produces a numerical value, and as such isn't entirely reliable to operate in logical evaluations. IMHO
Sum() or Sum() may not always work out as expected.
A better expression would be:
= IF (cat = 'VJ MS',
Pick(Match( 'VSP in %', 'Auftragseingang', usw ...)
num(Sum(VSP_Gesamt) / Sum(Umsatz_Gesamt), '#.##0%'),
num(Sum(AE_Gesamt), '#.#'),
usw...
)
)
Best,
Peter

Re: Too many formulas, HELP
Christina Müller Sep 21, 2016 8:07 AM (in response to Christina Müller)Thank you, for all your answers!
I tried a lot, but none of them worked....
I think the problem is, that expressions like 'Auftragseingang', VSP in % and all the titels are only there, so the programm can match it with the first column of the table, and select the right data for each row.
But maybe I am just misunderstanding something.
I tried somethin else (it is even more complex) and now only the number format is wrong.
The percentage shows 0,038% but it should show 38% ??
The problem is, that I cant show you the table because its confidential
=num(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],
etc......
)))))), '#.###,0')
&
num(
(sum(if(cat='VJ MS'and Titel='VSP in %', VSP_Gesamt,
if(cat='VJ MS'and Titel='Bruttoergebnis in %', [Bruttoergebnis absolut],
etc.....
)))
/
(sum(if(cat='VJ MS' and Titel='VSP in %', Umsatz_Gesamt,
if(cat='VJ MS' and Titel='Bruttoergebnis in %', Umsatz_Gesamt,
etc....
))),'#.##0%')
Thank you for trying to help me!