# Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

Highlighted
New Contributor II

## Count if, Aggr or Set Analysis

I have an problem getting my KPI widget to display the right number.

I have a table like this

 MSN Year FC FH A 2016 1500 3000 A 2017 1750 3125 A 2018 1600 3050 B 2016 1000 2250 B 2017 750 2000 B 2018 1000 2250 C 2016 2000 3600 C 2017 2000 3750 C 2018 2000 3750 D 2016 500 3500 D 2017 750 3125 D 2018 500 3050

Firstly I need to Aggr it by the Median FH and FC

Aggr(Median(FH),msn)

Aggr(Median(FC),msn)

I would expect an output like this

 MSN Med(FC) med(FH) A 1600 3050 B 1000 2250 C 2000 3750 D 500 3125

I then want to count all (distinct) MSNs that have a FH and FC Value <  limit set by a variable  \$(MedFH), \$(MedFC).

example:

\$(MedFC)=1500

\$(MedFH) = 3125

I would expect an end result of...

2 (MSN B and D match the criteria)

Here is what I have so far:

Count({\$<FC = {"<= \$(MedFC)"}, FH = {"<= \$(MedFH)"}>}DISTINCT MSN)

Im not sure how to incorporate the Aggr functions into the expression.

Labels (2)

• ### Count if

1 Solution

Accepted Solutions
MVP

## Re: Count if, Aggr or Set Analysis

You know what... you are right... try this

`Count(DISTINCT {<MSN = {"=Median(FH) < \$(MedFH) and Median(FC) < \$(MedFC)"}>} MSN)`
8 Replies
MVP

## Re: Count if, Aggr or Set Analysis

May be try this

```Count(DISTINCT Aggr(
If(
Aggr(NODISTINCT Median(FH), msn) < \$(MedFH) and
Aggr(NODISTINCT Median(FC), msn) < \$(MedFC),
MSN)
, MSN, Year))```

MVP

## Re: Count if, Aggr or Set Analysis

Or this

```Count(DISTINCT Aggr(
If(
Median(TOTAL <MSN> FH) < \$(MedFH) and
Median(TOTAL <MSN> FC) < \$(MedFC),
MSN)
, MSN, Year))```
New Contributor II

## Re: Count if, Aggr or Set Analysis

Cool!! Both work and come up with the same result. Looks like I was barking up the wrong tree with the set analysis.. It's very nice code. I understand most of it, I just don't understand why "Year" needs to be considered. Do you think you could explain it a little.
MVP

## Re: Count if, Aggr or Set Analysis

You know what... you are right... try this

`Count(DISTINCT {<MSN = {"=Median(FH) < \$(MedFH) and Median(FC) < \$(MedFC)"}>} MSN)`
New Contributor II

## Re: Count if, Aggr or Set Analysis

That returns 0...
MVP

## Re: Count if, Aggr or Set Analysis

I would have expected it to have worked... not sure why it didn't... but if you want to explore this option... I can help you if you are able to provide a sample where I can see the issue. If not, then you already have a solution based on Aggr() function.

Best,
Sunny

New Contributor II

## Re: Count if, Aggr or Set Analysis

Actually it was a small typo (I didn't say the real field names in my explanation)

It WORKS!!!! and......
It's about 1000 times faster than the AGGR method.
MVP

## Re: Count if, Aggr or Set Analysis

Awesome, I am glad that it finally worked and it is faster :).

Best,
Sunny

Community Browser