If-Sum then Count Distinct

[ksomosera10]

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!

[ksomosera10]

I tried this but still got nothing on my textbox

[sunny_talwar]

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])

[Anil_Babu_Samineni]

I am getting?

[ksomosera10]

Thanks Sunny! It worked!

[ksomosera10]

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

Cheers!

[ksomosera10]

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

[sunny_talwar]

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])

[Kushal_Chawda]

This should also work

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

[PrashantSangle]

Hi,

As per Henric today's post single quote with wild match character won't work correctly.

https://community.qlik.com/blogs/qlikviewdesignblog/2017/09/11/quotes-in-set-analysis

Regards,

[sunny_talwar]

Great, thanks for sharing this