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

[kunkumnaveen]

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

[kunkumnaveen]

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

current and previous year expressions

[kaushiknsolanki · Accepted solution]

For Prev Year.

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

For Current Year

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

Regards,

Kaushik Solanki

[Anonymous]

Hi Naveen,

Try this

Current Year Expression:

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

Prior Year Expression

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

[kunkumnaveen]

i have used ur current year

[kunkumnaveen]

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

[Kushal_Chawda]

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