Count if, Aggr or Set Analysis

[Tee_dubs]

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.

[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]

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 · Accepted solution]

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

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

[Tee_dubs]

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]

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