how to write expression

[kunkumnaveen]

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

[balar025]

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

[Kushal_Chawda]

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

[kaushiknsolanki]

Hi,

Try this expression.

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

See attached application.

Regards,

Kaushik Solanki

[kunkumnaveen]

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

[kunkumnaveen]

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

[balar025]

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

[Kushal_Chawda]

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)

[balar025]

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.

[kaushiknsolanki]

Ravi,

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

Regards,

Kaushik Solanki