topic Re: Set analysis candidate? in QlikView

Set analysis candidate?

Wed, 08 May 2013 20:56:36 GMT

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

Re: Set analysis candidate?

Wed, 08 May 2013 21:21:01 GMT

Since you're doing it for all items by year, can't you do (sum(listprice) - sum(baseprice))/sum(baseprice)?

Re: Set analysis candidate?

Wed, 08 May 2013 22:59:48 GMT

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

Re: Set analysis candidate?

sujeetsingh — Thu, 09 May 2013 04:17:33 GMT

Hi Bray ,

Just in simple words use your dimension and expression or post a small sample .

Re: Set analysis candidate?

Thu, 09 May 2013 13:16:58 GMT

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