Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I have the following issue.
i have the following tables in my analysis.
i can view either by Item or Year;
Item
Item | Qty | Gross Margin | ListPrice | BasePrice | Comparison |
---|---|---|---|---|---|
001 | 1 | 16 | 16 | 14 | (ListPrice - BasePrice)/BasePrice |
002 | 4 | 36 | 9 | 10 | -10% |
However when i wish to look at the Comparison over the year i would like to be able to view all Items that were sold in that year and divide them by their BasePrices to get a % Comparison. Now i recieve -Null, because the no item is selected.
Year
Year | Qty | Gross Margin | ListPrice | BasePrice | Comparison |
---|---|---|---|---|---|
2013 | 50 | 500 | 10 | = All Base Prices Added together | (50-Baseprice)/Baseprice |
2012 | 100 | 400 | 4 | = All Base Prices Added together | 4-Baseprice |
What i need to do here is make my tag a baseprice even when no item is selected for the Year dimension.
Any help is appreciated
Since you're doing it for all items by year, can't you do (sum(listprice) - sum(baseprice))/sum(baseprice)?
Thanks for the tip.
Is it possible to use expressions with this SUM logic?
Med vänlig hälsning
Bradley Ryan Coyne
Sap (BW) Konsult
Evry Consulting AB
bradley.coyne@evry.com<mailto:bradley.coyne@evry.com>
M +46 070 864 9051
Hi Bray ,
Just in simple words use your dimension and expression or post a small sample .
This is my Expression.
(sum(ListPrice_Exp)) - (sum(BaseListprice)) / sum( if(not isnull(BaseListprice),BaseListprice*Exchangerate/ExchangerateEUR))
ListPrice_Exp is an expression which gives you the actual selling price minus reduction ->
(NetSales / Qty).
BaseListprice is the Base
Example thatworks great is this (ListPrice_Exp - Baselistprice ) / BaseListprice; but this only allows a value when Item is selected.
when trying to view aggregated values over year or differnet product groups i need the sum function but cant get the syntax to work.
Thanks again