Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Set analysis candidate?

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
00111616 14(ListPrice  - BasePrice)/BasePrice
002436910-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
20135050010= All Base Prices Added together(50-Baseprice)/Baseprice
20121004004= All Base Prices Added together4-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

4 Replies
Not applicable

Re: Set analysis candidate?

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

Not applicable

Re: Set analysis candidate?

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

sujeetsingh
Honored Contributor III

Re: Set analysis candidate?

Hi Bray ,

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

Not applicable

Re: Set analysis candidate?

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

Community Browser