Need to category name in KPI based on different Columns

[akmalquamri]

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]

[vinieme12]

Ignore selections in field [AUDIT.DEALER_CODE]

=FirstSortedValue(KDNR,
-Aggr(Count({<UB_GES-={0},[AUDIT.DEALER_CODE]=>}KDNR), [AUDIT.DEALER_CODE]), 2

[akmalquamri]

Once we select any [AUDIT.DEALER_CODE] then it should calculate the second highest rank of KDNR based on selection

[Chanty4u]

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

)

[akmalquamri]

Still not working

[jochem_zw]

.

[Chanty4u]

Try this

=Aggr(

    if(

        Rank(

            Count({<UB_GES -= {0}>} KDNR),

            4

        ) = 2,

        FirstSortedValue(

            KDNR,

            -UB_GES

        )

    ),

    AUDIT.DEALER_CODE

)