Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a dataset whose fields are:
AUDIT.DEALER_CODE,
KEY_ANTRAG_ERROR,
KDNR,
UB_GES
Problem Statement:
In a KPI box, need to get the KDNR whose rank is 2, for UB_GES is not equal to 0 (Rank of KDNR needs to be calculated based on count of KDNR). This needs to work on dealer code level. Also if there are 2 KDNRs with the same rank, ranking should be done based on highest UB_GES value.
This needs to be done in the front end. No transformations in the script.
Ex:
For Dealer Code 10259,
KDNR 4211 and 4267 have the highest rank based on count.
Off the 2 KDNRs, 4511 has the highest UB_GES value of 3662.72 and 4267 has the least of 350.
So the KPI box should return value as 4267.
I tried: FirstSortedValue(KDNR,
-Aggr(Count({<UB_GES-={0}>}KDNR), [AUDIT.DEALER_CODE]), 2)
But when using [AUDIT.DEALER_CODE] filter getting null values in KPI. It should calculate the the second highest KDNR which is count of KDNR based on selected [AUDIT.DEALER_CODE]
Ignore selections in field [AUDIT.DEALER_CODE]
=FirstSortedValue(KDNR,
-Aggr(Count({<UB_GES-={0},[AUDIT.DEALER_CODE]=>}KDNR), [AUDIT.DEALER_CODE]), 2
Once we select any [AUDIT.DEALER_CODE] then it should calculate the second highest rank of KDNR based on selection
Try this.=Aggr(
if(
Rank(
Aggr(
Count({<UB_GES-={0}>} KDNR),
AUDIT.DEALER_CODE,
KDNR
),
AUDIT.DEALER_CODE
) = 2,
FirstSortedValue(
KDNR,
-UB_GES
)
),
AUDIT.DEALER_CODE
)
Still not working
.
Try this
=Aggr(
if(
Rank(
Count({<UB_GES -= {0}>} KDNR),
4
) = 2,
FirstSortedValue(
KDNR,
-UB_GES
)
),
AUDIT.DEALER_CODE
)