Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Contributor III

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

1 Solution

Accepted Solutions

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

16 Replies
balar025
Contributor III

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

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

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

kunkumnaveen
Contributor III

Re: how to write expression

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

kunkumnaveen
Contributor III

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

balar025
Contributor III

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

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
Contributor III

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

Community Browser