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: 
rahulsud007
Creator
Creator

Distinct Count of only those Items whose sum(QTy) is greater than 0

Dear All,

I  have a requirement in which I need distinct count of only those items whose sum(qty) is greater than 0.

In the attached QV app, the count of item nos should be 3 as sum(qty) for P2 is 0 hence it should not be counted.

Kindly suggest.

10 Replies
Not applicable

Hi!

You need to use somthing like that:

Count({<Item={"=Sum(qty)>0"}>} DISTINCT Item)

maxgro
MVP
MVP

Count({< Item={"=sum(QTY)>0"}>} Distinct Item)

jagan
Partner - Champion III
Partner - Champion III

Hi Rahul,

Try like this

Count({< Item={'=Sum(QTY)>0'}>} Distinct Item)

OR

Sum(Aggr(If(Sum(QTY)>0, 1), Item))

Regards,

Jagan.

qlikviewwizard
Master II
Master II

Good trick.‌

maxgro
MVP
MVP

yes but memory and calc time are better with set analysis than aggr

rahulsud007
Creator
Creator
Author

Thanks guys..:)

rahulsud007
Creator
Creator
Author

Dear All, I tried the above expressions in the chart using Pick Match but it is not working properly.

In the attached excel if we filter 2-3 Months removing the blank spaces the count we should get is 643 but here I am getting 645.

I have done using Pick Match and 0-1 Months 2-3 Months are bucket and I have Pivoted  the table.

@

Anonymous
Not applicable

hi,

If(Sum(QTY)>0,Count(Distinct Item))


or


Count({< Item={'=Sum(QTY)>0'}>} Distinct Item)

qlikviewwizard
Master II
Master II

Hi Rahul,

Please use the expressions as shown in screen. Hope this will help you. Thank you.

Capture.JPG