Forum Discussion

bapaul's avatar
bapaul
New Contributor
2 years ago
Solved

Top Ten and All Other

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

Reports
  • jzook's avatar
    jzook
    16 days ago

    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

7 Replies

  • jzook's avatar
    jzook
    New Contributor II
    16 days ago

    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

  • JackLacava's avatar
    JackLacava
    Icon for OneStream Employee rankOneStream Employee
    2 years ago

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

  • bapaul's avatar
    bapaul
    New Contributor
    2 years ago

    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.

    • rhankey's avatar
      rhankey
      Contributor III
      6 months ago

      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.

      • jzook's avatar
        jzook
        New Contributor II
        5 months ago

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

  • Vaibhvai_Soni's avatar
    Vaibhvai_Soni
    New Contributor
    4 months ago

    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