topic Re: If-Sum then Count Distinct in QlikView

If-Sum then Count Distinct

ksomosera10 — Tue, 12 Sep 2017 07:50:09 GMT

Hi!

Can you help me out on my problem?

I have this table and text box. Here's what I need. I want to show in my text box the distinct count of people having a specified criteria and condition.

Just to give you an idea, here's a sample data on my table:

| Spiderman | Web | Nature | 900 |

On my text box I want to show this kind of expression:

If Element == 'Nature' and HP <= 1000 Then Count Distinct Name

I hope this helps you understand my problems...

Thanks!

Re: If-Sum then Count Distinct

Kushal_Chawda — Tue, 12 Sep 2017 07:59:39 GMT

may be this

=count(distinct {<Element ={'Nature'},HP ={'<=1000'}>} Name)

Re: If-Sum then Count Distinct

Anil_Babu_Samineni — Tue, 12 Sep 2017 08:01:06 GMT

Try this?

If(Element = 'Nature' and HP <= 1000, Count(Distinct Name))

Count({<Element = {Nature}, HP = {'<=1000'}>} DISTINCT Name)

Re: If-Sum then Count Distinct

Kushal_Chawda — Tue, 12 Sep 2017 08:03:27 GMT

=count(distinct {<Name ={"=sum({<Element={'Nature'}>}HP)<=1000"}>} Name)

Re: If-Sum then Count Distinct

ksomosera10 — Tue, 12 Sep 2017 08:04:23 GMT

Thanks kushal,

By the way. I forgot to mention these

If Element == 'Nature' and SUM(HP) <= 1000 Then Count Distinct Name

I've put underline on it. I also need to sum up the HP. because some heroes have different HP but same names depending on their levels.

Re: If-Sum then Count Distinct

ksomosera10 — Tue, 12 Sep 2017 08:05:54 GMT

Thanks Anil,

I forgot to mention I need to sum the HP before I check if it is less than 1000.

If Element == 'Nature' and SUM(HP) <= 1000 Then Count Distinct Name

Thanks.

Re: If-Sum then Count Distinct

Anil_Babu_Samineni — Tue, 12 Sep 2017 08:06:42 GMT

This?

Count({<Element = {Nature}, HP = {"Sum(HP) <=1000"}>} DISTINCT Name)

Re: If-Sum then Count Distinct

Anil_Babu_Samineni — Tue, 12 Sep 2017 08:07:36 GMT

My bad, Missed equal sign

Count({<Element = {Nature}, HP = {"=Sum(HP) <=1000"}>} DISTINCT Name)

Re: If-Sum then Count Distinct

ksomosera10 — Tue, 12 Sep 2017 08:15:12 GMT

Hi Anil,

Thanks for helping out but All of my text box return's 0

Re: If-Sum then Count Distinct

Anil_Babu_Samineni — Tue, 12 Sep 2017 08:18:04 GMT

May be you don't have data for that? Can you split expression like and tell us which expression returns the value?

Count(DISTINCT Name)

Count({<Element = {Nature}>} DISTINCT Name)

Count({<HP = {"=Sum(HP) <=1000"}>} DISTINCT Name)

Re: If-Sum then Count Distinct

ksomosera10 — Tue, 12 Sep 2017 08:21:51 GMT

35 = Count(DISTINCT Name)

0 = Count({<Element = {Nature}>} DISTINCT Name)

5 = Count({<HP = {"=Sum(HP) <=1000"}>} DISTINCT Name)

Do you think there's something wrong with my expression?

Re: If-Sum then Count Distinct

Anil_Babu_Samineni — Tue, 12 Sep 2017 08:24:40 GMT

Great, Due to zero issuing facing, May be? Because with in the set analysis , can work as and operator that means It should count when 2 conditions filled. What i state earlier you may not having the Nature as value and Qlik is case sensitive. you can troubleshoot yourself because we don't have any copy file with me..

Re: If-Sum then Count Distinct

ksomosera10 — Tue, 12 Sep 2017 08:26:05 GMT

Do you want me to sen you a sample file?

Re: If-Sum then Count Distinct

Anil_Babu_Samineni — Tue, 12 Sep 2017 08:29:07 GMT

Off course

Re: If-Sum then Count Distinct

PrashantSangle — Tue, 12 Sep 2017 08:40:29 GMT

might be you are missing ' single quote around value Nature in set analysis {'Nature'}

Count({<Element = {'Nature'}, HP = {"=Sum(HP) <=1000"}>} DISTINCT Name)

Regards,

Re: If-Sum then Count Distinct

Anil_Babu_Samineni — Tue, 12 Sep 2017 08:43:14 GMT

I believe, Without that it works

Re: If-Sum then Count Distinct

sunny_talwar — Tue, 12 Sep 2017 08:44:36 GMT

What happens if you use this

Count({<Element = {'*Nature*'}>} DISTINCT Name)

Does this give you a value other than 0?

Re: If-Sum then Count Distinct

ksomosera10 — Tue, 12 Sep 2017 08:46:33 GMT

Please wait, I'll create a legit data so that you can check it for yourself.

Re: If-Sum then Count Distinct

ksomosera10 — Tue, 12 Sep 2017 08:51:57 GMT

Hi,

I've used a different data but tried to replicate my whole work problem. I hope this will help you out to check why I get these problems. thanks!

Re: If-Sum then Count Distinct

Anil_Babu_Samineni — Tue, 12 Sep 2017 08:55:41 GMT

Use this? Because, Sum is not proper with in the set

=Count({<Functional = {'F1'}, Programming = {'P1'}, nAlloc = {"=Sum(Alloc) <= 99"}>} DISTINCT [Resource Name])