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

[rahulsud007]

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.

[#2]

Hi!

You need to use somthing like that:

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

[maxgro]

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

[jagan]

Hi Rahul,

Try like this

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

OR

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

Regards,

Jagan.

[qlikviewwizard]

Good trick.‌

[maxgro]

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

[rahulsud007]

Thanks guys..:)

[rahulsud007]

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]

hi,

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

or

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

[qlikviewwizard]

Hi Rahul,

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