Post reply

Rank values based on dates (grouping)

  • October 21, 2021 at 12:35 am

    #3942657

    Hello friends,

    I need some help. I need to rank all titles for each week. Later on, I need to pull top20 titles for each week in PowerBI, however for some reason it doesn't let me to do it there (it pulls top20% only). I am trying to find a way around and group it in SQL. Does someone know how it is possible to get a column with the ranking for each week?

    SELECT

    [DMDUNIT], [STARTDATE], SUM([TOTFCST]) AS 'Total Forecast'

    FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC]

    GROUP BY [DMDUNIT], [STARTDATE], [TOTFCST]

     

    Forecast_grouping

  • October 21, 2021 at 5:53 am

    #3942707

    Calculate week numbers for each sale record, then you can do something like

    SELECT WeekNo = t.N, ProductID, ca.Total_Sales, ca.rnk 
    FROM Tally t
    CROSS APPLY (SELECT TOP 10 ProductID, Total_Sales
                    , rnk = RANK() OVER (t.N ORDER BY Total_Sales DESC)
                 FROM Sales
                 WHERE WeekNo = t.N) ca

    Tally is just a numbers table from 1 to some number (in this case at least 10).

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply