CUBESET TopCount problem with date range

[macfuller]

I am getting inconsistent results with the following CUBESET statement.
- returns the correct ranked values without the date range clause
- returns the correct ranked values when the start and end date are the same (taken from a timeline)
- returns out of order and duplicate values when start and end date are not the same.

=CUBESET("ThisWorkbookDataModel","TopCount(
(
[CC Detail].[Dept Category].[" & G14 & "],
[Account Tree].[Node 7 Descr].[" & $K$2 & "],
[CC Detail].[Facility].[" & $K$3 & "],
[Orders].[Origin].[" & $K$4 &"],
[Calendar].[Date].[" & $K$5 & "]:[Calendar].[Date].[" & $K$6 & "]",
[Vouchers].[Vendor Name].children
)
,10
,[Measures].[Voucher Amt]
)","Vendors for " & G14
)

Values are returned from the cubeset using CUBERANKEDMEMBER 1-10. Here are results from a 3 day range (amount retrieved in a separate column)

[TABLE="width: 301"]
<tbody>[TR]
[TD]Vendor1[/TD]
[TD] $7,630 [/TD]
[/TR]
[TR]
[TD]Vendor2[/TD]
[TD] $5,090 [/TD]
[/TR]
[TR]
[TD]Vendor3[/TD]
[TD] $3,629 [/TD]
[/TR]
[TR]
[TD]Vendor4[/TD]
[TD] $5,573 [/TD]
[/TR]
[TR]
[TD]Vendor5[/TD]
[TD] $3,680 [/TD]
[/TR]
[TR]
[TD]Vendor6[/TD]
[TD] $3,395 [/TD]
[/TR]
[TR]
[TD]Vendor7[/TD]
[TD] $6,809 [/TD]
[/TR]
[TR]
[TD]Vendor8[/TD]
[TD] $3,307 [/TD]
[/TR]
[TR]
[TD]Vendor7[/TD]
[TD] $6,809 [/TD]
[/TR]
[TR]
[TD]Vendor9[/TD]
[TD] $3,075 [/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]


I wish to return the top 10 vendors, based on voucher spend, for particular departments, General Ledger accounts, Locations, and Purchase Order types. The cells in each reference area contain the CUBERANKEDMEMBER of the respective slicers.

The file is extremely large and contains proprietary data so I can't post it unfortunately.

The calendar table and respective lookup tables have a relationship with the Purchase Order [Orders] table, which in turn has a relationship with the [Vouchers] table where the measure is taken from. The calendar file is marked as a date table and has all dates between any two points on the timeline.

I've tried adding .[All] to the date clause and a few other options but nothing seems to eliminate the inconsistent results. There is a similar question on Stack Overflow that I added a question to but the original question has had no response in several months. I'm hoping there's a simple syntax issue with the date clause.

 

[Matt Allington]

I believe the "correct" syntax for "children" is

[Orders].[Origin].&[Child]

You don't have the ampersand. Try adding that back in and see if it manages the consistency issue. In my experience it "can" work without it, but I think this is the "better" syntax

 

[macfuller]

Did you mean for vouchers? That's the only clause with .children. I tried your syntax but the cell returned #N/A.

 

[Matt Allington]

I don't think you can use .children at all (I could be wrong). I am not an expert but thought that this may be the problem. The & syntax is to extract a single child from the model, and you need to specify the exact value in place where I put 'child'. But I guess that is not going to do what you want - sorry.

 

[macfuller]

The syntax works fine with no date clause for all other variables. I check against a normal pivot table. It's only the date clause that introduces the variance. If I knew a way to have <= a date I'd try with just one date condition and see if it returns something to match the pivot table.

 

[SteZack]

I am getting inconsistent results with the following CUBESET statement.
- returns the correct ranked values without the date range clause
- returns the correct ranked values when the start and end date are the same (taken from a timeline)
- returns out of order and duplicate values when start and end date are not the same.

=CUBESET("ThisWorkbookDataModel","TopCount(
(
[CC Detail].[Dept Category].[" & G14 & "],
[Account Tree].[Node 7 Descr].[" & $K$2 & "],
[CC Detail].[Facility].[" & $K$3 & "],
[Orders].[Origin].[" & $K$4 &"],
[Calendar].[Date].[" & $K$5 & "]:[Calendar].[Date].[" & $K$6 & "]",
[Vouchers].[Vendor Name].children
)
,10
,[Measures].[Voucher Amt]
)","Vendors for " & G14
)

Values are returned from the cubeset using CUBERANKEDMEMBER 1-10. Here are results from a 3 day range (amount retrieved in a separate column)

[TABLE="width: 301"]
<tbody>[TR]
[TD]Vendor1[/TD]
[TD] $7,630[/TD]
[/TR]
[TR]
[TD]Vendor2[/TD]
[TD] $5,090[/TD]
[/TR]
[TR]
[TD]Vendor3[/TD]
[TD] $3,629[/TD]
[/TR]
[TR]
[TD]Vendor4[/TD]
[TD] $5,573[/TD]
[/TR]
[TR]
[TD]Vendor5[/TD]
[TD] $3,680[/TD]
[/TR]
[TR]
[TD]Vendor6[/TD]
[TD] $3,395[/TD]
[/TR]
[TR]
[TD]Vendor7[/TD]
[TD] $6,809 [/TD]
[/TR]
[TR]
[TD]Vendor8[/TD]
[TD] $3,307[/TD]
[/TR]
[TR]
[TD]Vendor7[/TD]
[TD] $6,809 [/TD]
[/TR]
[TR]
[TD]Vendor9[/TD]
[TD] $3,075[/TD]
[/TR]
</tbody>[/TABLE]


I wish to return the top 10 vendors, based on voucher spend, for particular departments, General Ledger accounts, Locations, and Purchase Order types. The cells in each reference area contain the CUBERANKEDMEMBER of the respective slicers.

The file is extremely large and contains proprietary data so I can't post it unfortunately.

The calendar table and respective lookup tables have a relationship with the Purchase Order [Orders] table, which in turn has a relationship with the [Vouchers] table where the measure is taken from. The calendar file is marked as a date table and has all dates between any two points on the timeline.

I've tried adding .[All] to the date clause and a few other options but nothing seems to eliminate the inconsistent results. There is a similar question on Stack Overflow that I added a question to but the original question has had no response in several months. I'm hoping there's a simple syntax issue with the date clause.

Hi,
i have the same problem, did you solve it?

 

[macfuller]

No, didn't get a solution. I even went to PowerPivotPro and had a consultant take a look, but they didn't find a resolution. I don't know if it's something I'm doing wrong or an inherent bug in PowerPivot.

I've got a measure-based workaround based on https://blog.crossjoin.co.uk/2012/07/11/powerpivot-top-n-reports-using-excel-cube-formulas/, but it's slower than the MDX solution.

 

[Matt Allington]

It is doesn't do what you want, that doesn't automatically make it a bug. Maybe it is just not designed to do what you want.