Hello, I have a safety KPI [Safety] which is ranked 0-1, and i am trying to make a table that in the dimension, returns the top 3 projects [CM1CTCODE] with the highest safety score, this i know how to do, however i am having issues because i need it specifically to only count which projects were top 3, last month [CalendarDate]. I have tried the following codes but nothing seems to work:
=if(AGGR(rank({<CalendarDate={'$(=$(vPreviousMonth))'}>}Safety,1,1),CM1CTCode)<=3,CM1CTCode,null()) // Returns too many entrys
={<Safety={"=Rank({<CalendarDate={'$(=$(vPreviousMonth))'}>}Safety)<=3"}>}CM1CTCode // just kind of returns, whatever
i have tried these both (and similar code) with different numbers in the Rank() function and nothing seems to work. I just need the dimension column to ONLY show any project with a Rank(Safety,1,1) of less than 3, so tied projects included
What is your KPI? If "Safety" is a field, the KPI could be e.g. Max(Safety) or Avg(Safety).
Is "CalendarDate" a date or a month? If both "CalendarDate" and "vPreviousMonth" represent the first date of the month, then your Set expression could work, but if it is a date, you need to tweak the Set expression and perhaps use CalendarDate={">=$(vPreviousMonth)"}.
What is your dimension in the chart? If it is "CM1CTCode", then a naked Aggr() could work, but if it isn't, then you need to wrap it in an aggregation function, e.g. Concat(Aggr(...),', ').
So try something similar to Concat( Aggr( If( Rank(Max({<CalendarDate={"$(vPreviousMonth)"}>} Safety),4,1)<=3, Max({<CalendarDate={"$(vPreviousMonth)"}>} Safety) ), CM1CTCode ), ', ' )
That hasnt worked, i need the output to be as similar as it can be to this example, this uses pivot tables but i need it to be the same output but instead of groups A and B it is the Different CalendarDates (Year Month info)
.png "Former Employee"){kind=icon}
