Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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

Re: Count only Sum of Sales is not equal to 0

Try

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

View solution in original post

10 Replies
Highlighted
Champion III
Champion III

Re: Count only Sum of Sales is not equal to 0

ttry this

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

Highlighted
Partner
Partner

Re: Count only Sum of Sales is not equal to 0

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

Highlighted
Contributor III
Contributor III

Re: Count only Sum of Sales is not equal to 0

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

Highlighted
Master III
Master III

Re: Count only Sum of Sales is not equal to 0

Try

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

View solution in original post

Highlighted

Re: Count only Sum of Sales is not equal to 0

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)

Highlighted
Creator
Creator

Re: Count only Sum of Sales is not equal to 0

try this...

Expression not tested

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

Highlighted
Contributor III
Contributor III

Re: Count only Sum of Sales is not equal to 0

This worked fine for me.

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

Thanks,

Venu

Highlighted
Partner
Partner

Re: Count only Sum of Sales is not equal to 0

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?

Highlighted

Re: Count only Sum of Sales is not equal to 0

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

Capture.PNG