ranking in pivot table

[ali_hijazi]

Hello

Got a pivot table grouping by TPK (test package) and month-year

I'm displaying for each month the ranking of the TPK for a specific expression

everything is working fine but what I want to display on the pivot is the first 10 TPKs which if I specify (TPK={$(=vHistory.TopXWasteTe)}) the ranking gives weird numbers

Please check attached document and advise

[ali_hijazi]

what i want is after I get the ranking display first 10 TPKs

[rubenmarin]

Hi Ali, vHistory.TopXWasteTe needs each value between single quotes, change vHistory.TopXWasteTe to:

=Chr(39) & Concat(DISTINCT {<

                    RECORD_TYPE={'waste'}

                    ,IS_WASTE={1}

                    ,Year=

                    ,Month=

                    ,MonthYear=

                    ,CALENDAR_DATE={'>= $(vFromDate) <= $(vMonthEndFromDate)'}

                    ,TPK={"=num(RANK(COUNT(DISTINCT

                                    {

                                        <

                                            RECORD_TYPE={'waste'}

                                            ,IS_WASTE={1}

                                            ,Year=

                                            ,Month=

                                            ,MonthYear=

                                            ,CALENDAR_DATE={'>= $(vFromDate) <= $(vMonthEndFromDate)'}

                                        >

                                    }

                                    TEST_ID

                                    )

                            ,4))<=$(vTopXTPK)"

                        }

                >}TPK, Chr(39) & ',' & Chr(39)) & Chr(39)

[ali_hijazi]

not accurate

click on Switch to Matrix view and see order

[ali_hijazi]

PAR.TPK.0002194|DEFAULT_2 is ranked 3 in Feb

[ali_hijazi]

I want same values stay as is when no filter is made on TPK but display only 10 records

[rubenmarin]

I don't see any PAR.TPK.0002194|DEFAULT_2, it's not listed on TPK values

[ali_hijazi]

ah sorry

check PAR.TPK.0001488|DEFAULT

without filtering its rank in Feb is 48

when filtering it gives 13

48 is the correct one

so keep displaying 48

[ali_hijazi]

SELECT FIRST JAN

see the top 10 then Feb should display the rank for the first 10 in JAN but compared to all TPKs