topic Count If Sum Is... in QlikView

Count If Sum Is...

ksomosera10 — Wed, 30 Aug 2017 06:35:21 GMT

Hi,

I would like to ask how to create a set analysis that can test if the Sum of a certain field is met before it will be included on the count.

I have this sample but I cannot get a good result:

=COUNT({1<KPI_ForProj={1}, nAlloc >= {'100'}>} [Name])

Just to explain some of these variables:

KPI_ForProj -> From script which gets the current month

nAlloc -> Numeric Field which consists of numbers. (100 is the target)

Name -> The person who got the nAlloc of 100

Required:

Get the sum of nAlloc per Name, if the nAlloc is equal of more than 100. It will be counted.

I hope I give my problem clear. Thanks!

I just need a set analysis because mine is not working.

THANKS!

tresB — Wed, 30 Aug 2017 06:40:50 GMT

May be this?

=COUNT({1<KPI_ForProj={1}, nAlloc = {'>=100'}>} [Name])

ksomosera10 — Wed, 30 Aug 2017 06:44:38 GMT

Hi Tresesco,

Thanks! I will try it out...

If its not a burden. can you teach me how you come up with that?

or explain how it works like that. I'm not really good at set analysis.

tresB — Wed, 30 Aug 2017 06:48:50 GMT

There would be many very good resources to learn from. You can search this community for the same to get links like:

ksomosera10 — Wed, 30 Aug 2017 06:51:45 GMT

Wow! That's great!

By the way. Just to check if my idea is right. If ever I need nAlloc lower than 30 does my set analysis would be like this?

=COUNT({1<KPI_ForProj={1}, nAlloc = {'<30'}>} [Name])

mayankraoka — Wed, 30 Aug 2017 06:55:14 GMT

Yes it will work.

Regards,

Mayank