Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
qlik_venu
Creator
Creator

Count only Sum of Sales is not equal to 0

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

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Try

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

View solution in original post

10 Replies
Chanty4u
MVP
MVP

ttry this

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

Anonymous
Not applicable

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
Creator
Creator
Author

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 1Pack 1a100
Item 1Pack 1b200
Item 3Pack 3a100
Item 3Pack 3b-50
Item 3Pack 3c-50
Item 6Pack 6a

0

antoniotiman
Master III
Master III

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
Not applicable

try this...

Expression not tested

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

qlik_venu
Creator
Creator
Author

This worked fine for me.

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

Thanks,

Venu

jeevays7
Partner - Creator III
Partner - Creator III

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

Capture.PNG