
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ttry this
Count({$<sales-={" 0 "}>} distinct Item_Id)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try
Count({<Item_id={"=Sum(Sales) <> 0"}>} distinct Item_id)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try this...
Expression not tested
=Count(if(Aggr(Sum(Sales),Item_Id)>0,Item_id))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This worked fine for me.
Count({<Item_id={"=Sum(Sales) <> 0"}>} distinct Item_id)
Thanks,
Venu


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Read about search string in set analysis here: A Primer on Set Analysis

- « Previous Replies
-
- 1
- 2
- Next Replies »