division calculation problem in script - group by issue?

[#1]

Hello. I'm struggling which a possibly very simple issue. In the sample qvw I have two fields, GrossProfit and Sales, and I want to calculate NetMargin (GrossProfit/Sales). I want to precalculate NetMargin in the script using sum(GrossProsit)/sum(Sales) as NetMargin. But then, when I evaluate sum(NetMargin) in the chart, I get incorrect values. On the other hand, when I evaluate sum(GrossProsit)/sum(Sales) formula as expression in chart, then I get correct values. I want to have NetMargin field precalculated in the script, not on the fly in the chart.

[swuehl]

You precalculated NetMargin per Period and Department, but then you want to aggregate over all Departments in your chart (because you only use Period dimension).

IMHO, you can't calculate this aggregation in any meaningful way only based on NetMargin, you would need to take a weight (Sales) back in per Departement, but I don't know if this is what you want.

Or either display your precalculated values with both Period and Department as dimensions, or calculate your NetMargin only grouped by Period.

Hope this helps,

Stefan

[Miguel_Angel_Baeyens]

Hi,

I agree with Stefan, actually the resulting table has the same number of records than the source, and that should not happen.

Check a possible solution as suggested by Stefan to what you are looking for.

Hope that helps.

Miguel

[#4]

This solution is not going to work. This is very simple example of a larger data set with more dimensions.In some cases, users need to be able to filter across 6 dimensions. Also, the chart in the sample qvw was created there just to help understand others what my problem is. In the app we have charts, gauges, text objects that display NetMargin. At the moment we are using variable (GrossProfit/Sales) to calculate NetMargin, but we will be adding more formulas (similar to NetMargin), and I would prefer to have them precalculated in the script, rather that using variables with expression to evaluate each one.

[swuehl]

Matthew, I understand that you want to precalculate values in the script for speed.

But precalculating most often means that you need to exactely define the scope / setting in which to use the precalculated values. You seldom can have both, flexibility (filtering values) and speed by precalculation.

As I tried to explain, you can't just aggregate a percentage like NetMargin. You'll need to use a weight to get the correct results, but then you would again introduce some complexity in your expressions, especially if your users could change dimensions.

You could probably precalculate other values, it is best to start with values that you just add up, like Sales, Quantities.

Regards,

Stefan