32 Replies Latest reply: Sep 12, 2017 7:34 AM by Sunny Talwar

# If-Sum then Count Distinct

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:

|      Name    |  Power        |  Element |    HP    |

|    Batman  |  Batpower  |      Dark  | 1000    |

| Spiderman |    Web        |  Nature  |  900    |

| Aquaman  |Trident Strike|    Water  |  2000  |

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

may be this

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

• ###### Re: If-Sum then Count Distinct

or

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

• ###### Re: If-Sum then Count Distinct

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

Try this?

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

OR

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

• ###### Re: If-Sum then Count Distinct

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

This?

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

• ###### Re: If-Sum then Count Distinct

My bad, Missed equal sign

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

• ###### Re: If-Sum then Count Distinct

Hi Anil,

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

• ###### Re: If-Sum then Count Distinct

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

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

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

Do you want me to sen you a sample file?

• ###### Re: If-Sum then Count Distinct

Off course

• ###### Re: If-Sum then Count Distinct

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

Like

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

Regards,

• ###### Re: If-Sum then Count Distinct

I believe, Without that it works

• ###### Re: If-Sum then Count Distinct

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

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

• ###### Re: If-Sum then Count Distinct

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

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

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

• ###### Re: If-Sum then Count Distinct

I tried this but still got nothing on my textbox

• ###### Re: If-Sum then Count Distinct

I am getting?

• ###### Re: If-Sum then Count Distinct

I've checked the script and you've got "Alloc" instead of "nAlloc"

• ###### Re: If-Sum then Count Distinct

May be this

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

• ###### Re: If-Sum then Count Distinct

Thanks Sunny! It worked!

• ###### Re: If-Sum then Count Distinct

I will translate it into the heroes data and see if this will be good enough

Cheers!

• ###### Re: If-Sum then Count Distinct

The only thing you might want to change is to use * before the equal sign in set analysis

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

• ###### Re: If-Sum then Count Distinct

This should also work

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

• ###### Re: If-Sum then Count Distinct

But if Resource Name have more than one Programming, then the combined Sum(nAlloc) could still be greater than 99 for instance....

Resource Name     Programming     nAlloc

ABC                         P1                         80

ABC                         P2                         20

Would you include it or exclude it? I guess depends on the user requirement and data. But our expressions are def. not the same

• ###### Re: If-Sum then Count Distinct

Bas bhai..tune bol diya to baat khatam.. expression alag he

• ###### Re: If-Sum then Count Distinct

Thank you yaar