using concatenate in a cubeset formula as well as a top count

Mike_Alex

New member
Joined
Jun 10, 2014
Messages
57
Reaction score
0
Points
0
Location
Middle Wisconsin
I have posted this question, and have asked others, but am unable to find a solid answer. I am hoping that in here, a solution can be found. I am trying to pull a child (second in a hierarchy) but only with the top count of 20. I created this formula that works for the topcount:

=CUBESET("data","topcount((["&H3&"].children),20,sum(([Time Periods].[Descriptive Fiscal Periods].[Fiscal Week].&["&F3&"]),[Measures].[Written Sales]))","Dim",2,"[Measures].[Written Sales]")

Formula works fine, and pulls the data in. H3=CUBEMEMBER("data","[Widgit Item Master].[Widgit Grouping - Widgit ID].[All].[Widgit 1]") and F3 is my fiscal date (=CUBEMEMBER("data","[Time Periods].[Descriptive Fiscal Periods].[Fiscal Week].&[Week Ended, May 31, 2014]")

Oddly enough, when I use the formula, it returns about half of my Widgit groupings, where I can view the data. Other half however, does not return (only #N/A).

Becuase of this, I went another route and used the descendants formula:

=CUBESET("data",CONCATENATE("Descendants([Retail Widgit Master].[Widgit Grouping - Widgit ID].[",$H$3,"],1)"),"Test",2,"[Measures].[Written Sales]") with H# as my cube member for Widgit 1. This worked as well, but I still want only the top count (of twenty). I have tried to add the topcount in front of the descendants, as well as after, and rearranging the calc many times, but all to no avail.

Is there a chance that someone could provide a solution or point me in a direction?
Much appreciated.
Alex~
 
Back
Top