topic Re: Exclude the data if sales is zero in QlikView

Exclude the data if sales is zero

vireshkolagimat — Sun, 14 Jan 2018 15:16:09 GMT

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.

Re: Exclude the data if sales is zero

petter — Sun, 14 Jan 2018 15:26:03 GMT

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?

Re: Exclude the data if sales is zero

pradosh_thakur — Sun, 14 Jan 2018 15:30:20 GMT

try

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

Re: Exclude the data if sales is zero

vireshkolagimat — Sun, 14 Jan 2018 15:30:41 GMT

I am trying to show in pivot table.

Re: Exclude the data if sales is zero

petter — Sun, 14 Jan 2018 15:32:32 GMT

It works in a Pivot Table too for me.

Which dimensions are you using?

Re: Exclude the data if sales is zero

vireshkolagimat — Sun, 14 Jan 2018 15:55:43 GMT

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.

Re: Exclude the data if sales is zero

petter — Sun, 14 Jan 2018 15:57:54 GMT

Are you using the Aggr()-function?

What are the dimensions you are using?

Re: Exclude the data if sales is zero

vireshkolagimat — Sun, 14 Jan 2018 16:05:34 GMT

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.