Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You know what... you are right... try this
Count(DISTINCT {<MSN = {"=Median(FH) < $(MedFH) and Median(FC) < $(MedFC)"}>} MSN)
May be try this
Count(DISTINCT Aggr( If( Aggr(NODISTINCT Median(FH), msn) < $(MedFH) and Aggr(NODISTINCT Median(FC), msn) < $(MedFC), MSN) , MSN, Year))
Or this
Count(DISTINCT Aggr( If( Median(TOTAL <MSN> FH) < $(MedFH) and Median(TOTAL <MSN> FC) < $(MedFC), MSN) , MSN, Year))
You know what... you are right... try this
Count(DISTINCT {<MSN = {"=Median(FH) < $(MedFH) and Median(FC) < $(MedFC)"}>} MSN)
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
Awesome, I am glad that it finally worked and it is faster :).
Best,
Sunny