Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
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