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)
<tbody>
</tbody><colgroup><col><col></colgroup>
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.
- 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)
| Vendor1 | $7,630 |
| Vendor2 | $5,090 |
| Vendor3 | $3,629 |
| Vendor4 | $5,573 |
| Vendor5 | $3,680 |
| Vendor6 | $3,395 |
| Vendor7 | $6,809 |
| Vendor8 | $3,307 |
| Vendor7 | $6,809 |
| Vendor9 | $3,075 |
<tbody>
</tbody><colgroup><col><col></colgroup>
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.
