Count only Sum of Sales is not equal to 0

[qlik_venu]

Hi,

I have to count the item_Id's only if the Sales is not equal to 0. Also the Sales needs to be summed up to the Item_Ids before the condition : Sales not equal to 0 is checked.

I am trying to use this, but I need the Sales to be Sum( Sales).

Count({$<sales={">0  < 0 "}>} distinct Item_Id)

Thanks,

Venu

[Chanty4u]

ttry this

Count({$<sales-={" 0 "}>} distinct Item_Id)

[Anonymous]

try this:

sum({<Item_id=E{<Sales={"0"}>} distinct Item_Id)>}>}Sales)

in words: inner E function exludes all sales with 0, sum only calculates sales <> 0

-> not sure if Syntax is correct

[qlik_venu]

The issue is with the Sales aggregation,

The data is as below,

When Sum ( Sales ) for Item 3 is 0, the count should drop the Item 3.

For the below data,

Count (  {<  Sum(Sales) should not be equal to 0 >} Item ID)

The above should return me 1 ( Item 1 ) alone.

Item 3 Sums up to 0. ( 100 + (-50) + (-50)  = 0)

Item 6 is by itself a 0.

Thanks.

	Sales
Item 1	Pack 1a	100
Item 1	Pack 1b	200
Item 3	Pack 3a	100
Item 3	Pack 3b	-50
Item 3	Pack 3c	-50
Item 6	Pack 6a	0

[antoniotiman · Accepted solution]

Try

Count({<Item_id={"=Sum(Sales) <> 0"}>} distinct Item_id)

[sunny_talwar]

I think you can try what antoniotiman‌ has provided below:

Count({<Item_id={"=Sum(Sales) <> 0"}>} distinct Item_id)

Alternatively, a less efficient option can be like this:

Sum(Aggr(If(Sum(Sales) <> 0, 1, 0), Item_id)

[Anonymous]

try this...

Expression not tested

=Count(if(Aggr(Sum(Sales),Item_Id)>0,Item_id))

[qlik_venu]

This worked fine for me.

Count({<Item_id={"=Sum(Sales) <> 0"}>} distinct Item_id)

Thanks,

Venu

[jeevays7]

Hi Sunny,

Here,

Count({<Item_id={"=Sum(Sales) <> 0"}>} distinct Item_id)

how will you give "=Sum(Sales) <> 0" in above expression.

if sum(sales) will give the result like this 100.34,203.1, etc,

and Item_id is 1,2,3,4,5,6.

Then how it will set item_id={"100.34"} and how it will take item_id of 100.34?

could you explain please.

or any reference blog is there?

[sunny_talwar]

Read about search string in set analysis here: A Primer on Set Analysis