Qlik Community

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
Tee_dubs
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

MSNYearFCFH
A201615003000
A201717503125
A201816003050
B201610002250
B20177502000
B201810002250
C201620003600
C201720003750
C201820003750
D20165003500
D20177503125
D20185003050

 

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

MSNMed(FC)med(FH)
A16003050
B10002250
C20003750
D5003125

 

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)
1 Solution

Accepted Solutions

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

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

 

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))
Tee_dubs
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.

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)
Tee_dubs
New Contributor II

Re: Count if, Aggr or Set Analysis

That returns 0...

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

Tee_dubs
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.

Re: Count if, Aggr or Set Analysis

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

Best,
Sunny