Results 1 to 2 of 2

Thread: How to remove an empty return from a topcount cubevalue formula

  1. #1
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0

    How to remove an empty return from a topcount cubevalue formula



    Register for a FREE account, and/
    or Log in to avoid these ads!


    I have been struggling with this calc for a while now, turning it around and sorting it anyway I can, but cannot seem to get straight. May be a bit of a challenge, and am hoping the experts on this site may be able to resolve, know you are very good at what you do.



    I am using an OLAP cube for a data source, and am trying to get a topcount that will not return any empty values. Here are the calcs that I am running:



    Measure would be: =CUBEMEMBER("OLAP Cube","[Measures].[Sales]")



    Row would be at: =CUBEMEMBER("OLAP Cube","[Store Location].[My Store]")



    For the measure, I want to sort by top sales by item groupings, so I use a top count formula (Excel will not take the entire formula so put into text for Excel to understand the MDX part): =("topcount(([Retail Master].[Item Grouping].children),10,sum(([Time Periods].[Fiscal Year].[All].[2014]),[Measures].[Sales]))")



    In turn, I put this into a cubeset formula to pull in the value: =CUBESET("OLAP Cube",(cell value that holds the topcount formula),"Cubeset")


    This all works fine, and I use the formula =CUBERANKEDMEMBER("OLAP Cube",(cell that holds my cubeset formula),ROW(A1))


    For the value, it is =CUBEVALUE("OLAP Cube",(cell that holds the store location),(cell that holds my measure which is a cubemember formula with the measure),(cell that holds the item grouping in the row, or the cuberankedmember returned value).



    The problem with the formula is when it returns the data, there is one member that reads =CUBEMEMBER("OLAP Cube ","[Retail Master].[Item Grouping].&[]") and it returns as a ranked member.



    So, here is the question, can I incorporate a nonempty into the text formula (or MDX segment) so that it will skip over this value and not pull in the empty?


  2. #2
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    Actually, it also does not sort right, so the MDX segment is also wrong there. If you could assist, would be eternally grateful...
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •