Short summarize: I want to divide the sale of one product group and sum it into two other groups.
Well, now I have tried to do the calculation in Universe instead (can’t do it in the ETL since it would impact other reports). What I have done so far is that I created two objects, first one where I calculated 40% of the sale and the other one 60%, of the same product (let’s call it A), using Case-When. That works great in the infoview report.
In the third object I want these two previous values to be sum together with two other product groups. I used another Case-When function here;
CASE
WHEN EDW_Mart.dbo.DIM_Article.Groupnumber =B
THEN EDW_Mart.dbo.FAK_Order.Sales + @Select(Balance\Sales (Kg.) Group B)
WHEN EDW_Mart.dbo.DIM_Article.Groupnumber =C
THEN EDW_Mart.dbo.FAK_ORDRE.KVANTUM + @Select(Balance\Sales (Kg.) Group C)
else EDW_Mart.dbo.FAK_Order.Sales
END
i.e. I want to sum the normal sale in Kg for B and C + the 40 & 60% from product A to show up in the Infoview report. The report is of course constructed so that the sales will be sorted per product group.
What happens is that B and C instead comes with the normal value x2. Have I done something wrong with the Case - When? Can I solve it in another way?
To start with %'s are best calculated on the report, as a SQL % is only correct at run time, i.e. if you slice and dice the data the % will not recalculate.
As for your Case statement you have no aggregation set in the SQL, so it looks like the results are not getting aggregated.
Normally, yes. But, since I want to slice the result from one group and add it to two other groups I get problem since I need to sort the table on product group.
You said that I didn’t seem to have an aggregate function, do you then think about sum()? I tried that too, without the desired result…
Yes, I meant wrapping the Case in a sum, like so:-
Sum(CASE
WHEN EDW_Mart.dbo.DIM_Article.Groupnumber =B
THEN EDW_Mart.dbo.FAK_Order.Sales + @Select(Balance\Sales (Kg.) Group B)
WHEN EDW_Mart.dbo.DIM_Article.Groupnumber =C
THEN EDW_Mart.dbo.FAK_ORDRE.KVANTUM + @Select(Balance\Sales (Kg.) Group C)
else EDW_Mart.dbo.FAK_Order.Sales
END)
Thx, but isn’t the SUM() function as you describes it having the same function as when I enter the object for editing. Then I go to the “Properties” tab, selecting the qualification as Measure and where it says “Choose how this measure will be projected when aggregated:” I choose “Sum”?
Sorry for my newbie questions, I’m pretty new into working with Universe designer.
(BOB member since 2002-06-20)
(BOB member since 2005-01-06)