Announcements
cancel
Showing results for
Did you mean:
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
Master III

Try

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

10 Replies
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

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 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
Master III

Try

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

MVP

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))

Creator
Author

This worked fine for me.

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

Thanks,

Venu

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?