Exclude the data if sales is zero

[vireshkolagimat]

Hi All,

I've the below scenario where Qlikview adding the both the rows to get the sum and giving the result by adding both the rows.

PRODUCTCODE	INVOICENO	INVOICEDATE	SALESCOST	SALESLISTVALUE	SALESQTY	SALESVALUE
1238007	185111000190	07-JAN-18	937.25	20.5	50	20.5
1238007	185111000190	07-JAN-18	187.45	0	10	0

In the above case net sales = SALESQTY * SALESVALUE= 1025

But i am getting 1230. SO it is taking the salesqty from next row, adding it to the first row and then giving the net sales.

While calculating the net sales i tried to use the set analysis like sum({<salesvalue-={0}>}salesvalue*salesqty)

But no change in the results.

[petter]

I can't seem to be able to reproduce anything other that the correct value which is 1025:

Where did you calculate this? In what type of QlikView Sheet Object? In a chart?

[pradosh_thakur]

try

sum({<PRODUCTCODE={"=(salesvalue>0)"}>}salesvalue*salesqty)

[vireshkolagimat]

I am trying to show in pivot table.

[petter]

It works in a Pivot Table too for me.

Which dimensions are you using?

[vireshkolagimat]

I am using that in a self service report. The data set is big and also the application. When i test it independently even i got the result. But  when i run in my application, not getting the expected result.

thanks for the help.

[petter]

Are you using the Aggr()-function?

What are the dimensions you are using?

[vireshkolagimat]

Hi I am not using the aggr() function . I've created a self service report where the user can select multiple dimensions like division, region, product code etc and measures such as net sales, gross sales, sales cost etc as per their requirements.