I hope someone can help me with an issue with calculations
I have a report that contains multiple lines and I need to be able to calculate %'s based on different lines ... for example
£ %
Sales 100000
Returns -5000 -5% these figures are based on Sales
Retros -1000 -1%
Total 96000
I suppose its the same as you would be able to do in excel, we have moved from Crystal reports which was relatively easy to do this
Any help gratefully received, I have attached a screen shot of the report
Sorry to bump this, but any ideas?
I haven't got time to test this I'm afraid, but do you need something like
[Revenue] / total(if[Category] = 'Sales') then ([Revenue]) else (0))
Hello Darners,
Can you please provide more information on how the data is laid out in the Query that is driving this report. Are Sales, Returns and Retros different columns/data items in the query? If yes, then a simple calculation like Returns/Sales or Retros/Sales will do the trick.
If Sales, Returns and Retros are actually values of the same column lets say [Measure Name] and another column [Measure Value] holds the actual numbers then your calculation would be
total(CASE WHEN [Measure Name]='Returns' THEN [Measure Value] ELSE 0 END)
/
total(CASE WHEN [Measure Name]='Sales' THEN [Measure Value] ELSE 0 END)
Similarly for Retros.
You may also attach the report XML here.
-Cognos810