Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Tee_dubs
Contributor III
Contributor III

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
sunny_talwar

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

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

View solution in original post

8 Replies
sunny_talwar

May be try this

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

 

sunny_talwar

Or this

Count(DISTINCT Aggr(
   If(
      Median(TOTAL <MSN> FH) < $(MedFH) and
      Median(TOTAL <MSN> FC) < $(MedFC), 
   MSN)
, MSN, Year))
Tee_dubs
Contributor III
Contributor III
Author

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

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

Count(DISTINCT {<MSN = {"=Median(FH) < $(MedFH) and Median(FC) < $(MedFC)"}>} MSN)
Tee_dubs
Contributor III
Contributor III
Author

That returns 0...
sunny_talwar

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
Contributor III
Contributor III
Author

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

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

Best,
Sunny