16 Replies Latest reply: Jul 13, 2017 6:27 AM by kushal chawda

# how to write expression

Hello All,

i need to show the list of all suppliers associated with a part but with nil transactions in the chosen period.let say i have sample data

year  Supplier    part      spend

2017      xxx        aaa          100

2017      yyy        bbb          200

2017      zzz        ccc          0

2017      zzz        ccc          100

2017      vvv        ddd          0

2016      yyy        bbb          0

2016      zzz        ccc          100

2016      zzz        ccc          0

2016      vvv        ddd          20

2015      yyy        bbb          10

2015      zzz        ccc          100

2015    zzz        ccc            50

2015      vvv        ddd          20

and if i choose 2017 so i need see below data(suppliers current year spend is nil and having some spend in previous year )

supplier         part        currentyear(2017)   previousyear(2016)

zzz              ccc           0                       100

vvv              ddd           0                       20

if i choose 2016 so i need see below data

supplier         part        currentyear(2016)   previousyear(2015)

yyy              bbb           0                     10

zzz              ccc           0                     150 (100+50)

I have not clue how to write a expression to achieve this requirement ,can any suggestion plz

thanks

• ###### Re: how to write expression

Hi,

You can have 2 expression

one is if(sum(Spend)=0,sum(Spend))

2nd is sum({<Year={\$(=Year-1)}>}Spend)

But in your data there is

2017      zzz        ccc          0

2017      zzz        ccc          100

So what should be the result?

Thanks

Ravi Balar

• ###### Re: how to write expression

the result is

supplier         part        currentyear(2017)   previousyear(2016)

zzz              ccc           0                       100

vvv              ddd           0                       20

it should consider only this

2017      zzz        ccc          0

• ###### Re: how to write expression

then use below formula's

First 1: if(isnull(FirstSortedValue(Aggr(sum(Spend),Supplier,Part,Year),1)) or FirstSortedValue(Aggr(sum(Spend),Supplier,Part,Year),1)=0,0)

Second: if(Column(1)=0,sum({<Year={\$(=Year-1)}>}Spend))

Regards,

Ravi Balar

• ###### Re: how to write expression

when you select 2017 what will happen to XXX & YYY?

• ###### Re: how to write expression

HI ,it should not display those suppliers because there spend is not zero

• ###### Re: how to write expression

Put the below expressions

Current Year:

Sum({<spend={"=0"}>}spend)

As suggested by Kaushik

Previous Year:

Sum({<Supplier =p({<spend = {"0"}>}),year = {"\$(=max(year)-1)"}>}spend)

• ###### Re: how to write expression

Hi,

Try this expression.

Sum({<Supplier =p({<spend = {"0"}>}),year = {"\$(=max(year)-1)"}>}spend)

See attached application.

Regards,

Kaushik Solanki

• ###### Re: how to write expression

Just observation, I think he is looking for current year value along with previous year value.

I tried with your solution but it is giving me sum of all values.

• ###### Re: how to write expression

Ravi,

Have a look at the attached application. That will give you clear picture.

Regards,

Kaushik Solanki

• ###### Re: how to write expression

can u post ur expressions as i am not allowed to copy this qvw to server

• ###### Re: how to write expression

can u post ur expressions as i am not allowed to copy this qvw to server

current and previous year expressions

• ###### Re: how to write expression

For Prev Year.

Sum({<Supplier =p({<spend = {"0"}>}),year = {"\$(=max(year)-1)"}>}spend)

For Current Year

Sum({<spend = {"0"}>}spend)

Regards,

Kaushik Solanki

• ###### Re: how to write expression

i have used ur current year

• ###### Re: how to write expression

add previous year expression as well, because zero value is getting suppressed

• ###### Re: how to write expression

Sum({<spenD = {"0"}>}spenD)

• ###### Re: how to write expression

Hi Naveen,

Try this

Current Year Expression:

Sum({<year={"=max(year)"}>}spend)

Prior Year Expression

Sum({<year={"=max(year)-1"}>}spend)