topic Re: how to write expression in QlikView

how to write expression

kunkumnaveen — Thu, 13 Jul 2017 09:35:50 GMT

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

balar025 — Thu, 13 Jul 2017 09:48:04 GMT

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

Kushal_Chawda — Thu, 13 Jul 2017 09:48:31 GMT

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

Re: how to write expression

kaushiknsolanki — Thu, 13 Jul 2017 09:52:06 GMT

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

kunkumnaveen — Thu, 13 Jul 2017 09:56:23 GMT

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

Re: how to write expression

kunkumnaveen — Thu, 13 Jul 2017 09:58:51 GMT

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

balar025 — Thu, 13 Jul 2017 10:03:06 GMT

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

Kushal_Chawda — Thu, 13 Jul 2017 10:05:51 GMT

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

balar025 — Thu, 13 Jul 2017 10:11:07 GMT

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

kaushiknsolanki — Thu, 13 Jul 2017 10:13:56 GMT

Ravi,

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

Regards,

Kaushik Solanki

Re: how to write expression

kunkumnaveen — Thu, 13 Jul 2017 10:16:08 GMT

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

Re: how to write expression

kunkumnaveen — Thu, 13 Jul 2017 10:17:05 GMT

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

kaushiknsolanki — Thu, 13 Jul 2017 10:18:28 GMT

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

Anonymous — Thu, 13 Jul 2017 10:22:23 GMT

Hi Naveen,

Try this

Current Year Expression:

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

Prior Year Expression

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

Re: how to write expression

kunkumnaveen — Thu, 13 Jul 2017 10:24:20 GMT

i have used ur current year

Re: how to write expression

kunkumnaveen — Thu, 13 Jul 2017 10:26:47 GMT

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

Re: how to write expression

Kushal_Chawda — Thu, 13 Jul 2017 10:27:04 GMT

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