How to get Dimension value in Expression

[Anonymous]

How to get Dimension value in expression of pivot table

consider a table

now here i want sum of sales depending on dimension

office eg if office = cape town then sum of sales is null

if office = Lome  then sum of sales is sales+500

if office = kuala lumpur then sum of is sales+450

means it entirely depends on dimension what value of sales should be displayed

[#2]

HI sachin. You can return the dimension in the expression by referring to it.

You've almost answered your own question there sachin. If there is a small number of offices, you could try a nested if statment

eg: if([office field] = 'Cape Town' , null(), if([Office field] = 'Lome', sum(sales) + 450.....

You could also do a pick / match combination to return a function based on the office, eg

=pick(match('Cape Town','Lome','Kuala Lumpur'),null(),sum(sales)+500,sum(sales)+450)

this link explains it really well: http://qlikviewnotes.blogspot.com/2008/10/match-function.html

Probably the best solution, and one that allows flexibility, consistency and the option to easily add more offices, would be to create a separate look up table with a new field that has an expression linked to each office.... you could then evaluate the expression using a dollar sign expansion on that field.

eg:

Office	Exp_Office
Cape Town	=null()
Lome	=sum(Sales)+500

Erica

[vijay_iitkgp]

Hi Sachin,

You can create inline/Excel table with Office and Additional values as Column and use the Expression Sum(Amount)+ Sum(Additional Values) .

Inline Table:

Office, Additonal Values

Cape Town, Null()

Lome,500

....

..

Hope this will help .

Regards

Vijay