Skip to main content
B
bapaul
November 8, 2023
Solved

Top Ten and All Other

  • November 8, 2023
  • 5 replies
  • 0 views

Hi.  I have a cube view / customer sales report where I can get my top ten customer sales, and I have a row that has the total sales.  How can I make a row to show 'All Others' so I can foot the lines to the total sales?  I've tried a CVR calc but it pulls in the top customer only.

Thanks,

Brian

    Best answer by jzook

    I have accomplished the "All Other" total using a U8 formula (RankedTotal) that calculates the accumulation of the records but in a corresponding column.  Then I used a formula in the original data row to capture that total and perform the calculation.  This is used in Conjunction with the golfstream busines rule XFR_MemberListRanked.

    https://community.onestreamsoftware.com/discussions/Reporting/cube-view-running-sum/4947/replies/5583

    5 replies

    J
    JackLacava
    November 10, 2023

    Can you post the definitions of the rows in question, please?

    B
    bapaulAuthor
    November 10, 2023

    This is my first line that brings back the top ten (U5 is my Customer field):

    U5#Top.CustomMemberList(BRName = CustomerRank, MemberListName = Ranked, RankType = Top, RankCount =  10, LoopMemberFilter = U5#root.Base, DataCellToRankMemberFilter = Cb#CustomerData:E#|CVEntity|:P#|CVParent|:C#USD:S#|CVScenario|:T#|CVTime|:V#|CVView|:A#Sales:F#|CVFlow|:O#Top:I#Top:U1#|CVUD1|:U2#|CVUD2|:U3#|CVUD3|:U4#|CVUD4|:U5#|CVUD5|:U6#|CVUD6|:U7#|CVUD7|:U8#|CVUD8|)

    And my second row for total sales is:  U5#Top:Name(Total Sales).

    So I'm looking for a single line that is essentially...total sales -  sum of the top ten results.  I'm sure it's something simple.

    J
    jzook
    May 9, 2025

    Is there a solution to this?

    R
    rhankey
    May 9, 2025

    I don't spend much time building Cube Views, but the most obvious option I can think of would be to have the Cube View compute the difference between the TopN and the Total rows, in much the same manner as you would include something like a Variance row or column.

    J
    jzook
    May 27, 2025

    Yes but I cannot get the total of the TopN... in order to calculate that variance.

    V
    Vaibhvai_Soni
    July 7, 2025

    Hi Bapaul,

    I also want to create top 20 customer list on the basis of revenue, but instead of UD5 member, we're using UD 1 member to maintain customer list.

    bapaul​, Can you please help me in creating the business rule?

    Thanks in advance
    Vaibhavi

    J
    jzookAnswer
    September 30, 2025

    I have accomplished the "All Other" total using a U8 formula (RankedTotal) that calculates the accumulation of the records but in a corresponding column.  Then I used a formula in the original data row to capture that total and perform the calculation.  This is used in Conjunction with the golfstream busines rule XFR_MemberListRanked.

    https://community.onestreamsoftware.com/discussions/Reporting/cube-view-running-sum/4947/replies/5583

    Terms & ConditionsAccessibility statement