New to Qlik Community

Discussion board for questions on how to use Qlik Community and its features.

Announcements
This forum is for questions and information about how to use the Qlik Community.
Please do not post product related questions here.
Select the correct forum from: Qlik Product Forums
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