topic Re: Struggling to Rank by a KPI for a specific Date in App Development

Struggling to Rank by a KPI for a specific Date

Tian-Jay — Mon, 17 Feb 2025 17:12:09 GMT

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

Re: Struggling to Rank by a KPI for a specific Date

hic — Tue, 18 Feb 2025 10:15:44 GMT

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

Re: Struggling to Rank by a KPI for a specific Date

Tian-Jay — Tue, 18 Feb 2025 13:29:33 GMT

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)