Creating a CUBESET function that returns a cube filtered by a date range

A

Andy Somnifac

New Member
Joined
Aug 16, 2016
Messages
7
Good morning everyone. I'm working on something and I need (or would like) to be able to create a CUBESET that is filtered based on a date range with a start and end date defined on the sheet.

Let's imagine the data is in the DOM, pulled in via a SQL query. The data connection can be called "DataModel" and we'll just call the table "Table". We'll give this hypothetical data a very simple structure of 2 columns:

Code: 
[CaseID] [DateValue]
00000000 01/01/2024
11111111 01/02/2024
22222222 01/03/2024
33333333 01/04/2024
44444444 01/05/2024
55555555 01/06/2024
66666666 01/07/2024
77777777 01/08/2024
88888888 01/09/2024
99999999 01/10/2024

Ultimately, I'd like to have a CUBESET function return a cube containing CaseID's based on the DateValue column, give a start and an end date. Let's sat I want it to return all CaseID's between 01/02/2024 and 01/05/2024 (which are in cells B1 and B2 on the same sheet as the CUBESET function). So, I'd be looking for it to return a cube with 4 members:

11111111
22222222
33333333
44444444

Is what I'm trying to do even possible? Am I barking up the wrong tree? If so, is anyone able to offer any tips or hints to get me a bit closer to this one?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Do you mean something like this?

VBA Code: 
Function Fu_CUBESET(DataRNG As Range, StartDate As Date, EndDate As Date)
' DataRNG is range with CaseID and DateValue columns, No headers in range
' StartDate is cell address to read StartDate
' EndDate is cell address to read EndDate
' ******************************** USAGE ******************************************************
' =Fu_CUBESET(A5:B14,B1,B2)
' =Fu_CUBESET(DataRange,StartDate,EndDate)
' ****************************************************************************************************
Dim iRow As Variant, i As Long
Dim StrARR() As String
Dim coll As New Collection
For Each iRow In DataRNG.Rows
    If iRow.Cells(1, 2).Value >= StartDate And iRow.Cells(1, 2).Value <= EndDate Then
        i = i + 1
        ReDim Preserve StrARR(1 To i)
        StrARR(i) = iRow.Cells(1, 1).Value
    End If
Next iRow
    Fu_CUBESET = Application.Transpose(StrARR)
End Function

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs." :devilish:
 
Upvote 0
@Tupe77

I can absolutely see times where I will find this useful (and I will definitely keep that in my list solutions to use), but in this case perhaps not. If I'm understanding it correctly, this would assume the table is a range on a sheet, rather than in a data model in the workbook itself. I apologize if I've misused any terms, since my training and learning about this subject has been very much by the seat of my pants.

Example of what I have done in other workbooks:

In other workbooks I'll have a my data pulled in via a SQL query. Usually I'll just need to pull data for a specific month, so I'll have a formula in a single cell that reads something like:

=CUBESET("DataModel", "([Table].["&C1&"],[Table].[CaseID].children)","Test Table")

(C1 has a value for the month I'm pulling)

Now my need has evolved to need to pull a 12 week period, which doesn't fit nicely into a single month. I'll be using a column in the table that contains a data that corresponds to the date that starts the week the Case was generated. My example in the OP just simplifies that.

Thanks,
 
Upvote 0
Oops, I see an error in my example in post #3 (and I don't see a way to edit):

=CUBESET("DataModel", "([Table].[Month].["&C1&"],[Table].[CaseID].children)","Test Table")

This would be the correct formula. Post #3 left out the [Month] column.
 
Upvote 0
To get a set of the dates, you'd want something like:

Excel Formula: 
=CUBESET("ThisWorkbookDataModel","Filter([Table1].[DateValue].[DateValue].Members,[Table1].[DateValue].MemberValue >= "&A1&" AND [Table1].[DateValue].MemberValue <= "&A2&")")

so I presume you'd just combine that Filter result in with your [Table].[CaseID].children
 
Upvote 0
Rory,

Thanks for the input. I'd knocked on the door of adding a filter function to the set expression, but everything I've tried has yielded a #N/A upon calculation.

To make sure I wasn't accidentally mixing text dates and date values I went as far as adding a calculated column to my DOM (called StartDateValue) that just takes the result of a DATEVALUE function on the column that indicates the start of week date for that particular row. I have a section of a worksheet that has 12 rows/2 columns that have the start date of 12 consecutive weeks un both human readable (rows 1 through 12 of column B) and Excel serial number formatted (rows 1 through 12 or column C) dates.

Using your sample above, I entered the following formula:

=CUBESET("ThisWorkbookDataModel","Filter([Table].[StartDateValue].[StartDateValue].Members,[Table].[StartDateValue].MemberValue >= "&B1&" AND [Table].[StartDateValue].MemberValue <= "&B12&"),[Table].[CaseID].children","Test Formula")

The 12 week period shown on the sheet starts the week of 11/6/2023 and ends the week of 1/22/2024. The data in the table being queried does include plenty of data to cover that time frame.

To my eye things look OK with that formula, but perhaps I'm missing something?
 
Upvote 0
Another update/correction:

The formula pasted above was from before I had tried the DATEVALUE approach. The B1 and B12 in the formula should read C1 and C12 accordingly.
 
Upvote 0
Oh - try this:

Excel Formula: 
=CUBESET("ThisWorkbookDataModel","Crossjoin(Filter([Table].[StartDateValue].[StartDateValue].Members,[Table].[StartDateValue].MemberValue >= "&B1&" AND [Table].[StartDateValue].MemberValue <= "&B12&"),[Table].[CaseID].children)","Test Formula")
 
Upvote 1
Solution
RoryA said:
Oh - try this:

Excel Formula: 
=CUBESET("ThisWorkbookDataModel","Crossjoin(Filter([Table].[StartDateValue].[StartDateValue].Members,[Table].[StartDateValue].MemberValue >= "&B1&" AND [Table].[StartDateValue].MemberValue <= "&B12&"),[Table].[CaseID].children)","Test Formula")
Click to expand...

This did it. Thanks for the help.

This did some things I didn't expect when I adapted it to create cubes of other data in the table, but I can get around that. When creating a cube of a column that doesn't contain unique values, but still based on the date, the cube contains duplicate values if a value exists for multiple dates. That probably makes sense and I should have expected that. Maybe there's a way to return all unique values, but that's another wall to bang my head on. This particular one is answered.

Thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
DOUBLE FILTER function to return ONLY records with most recent date
Replies
2
Views
359
elcalvo
E
N
  • Question
PowerQuery - Using Parameter in SQL query before filtering [huge datasource]
Replies
4
Views
2K
Nyanko
N
D
  • Question
Cubeset (Date Range) - Members that don't exist
Replies
2
Views
4K
Dtex20
D
F
  • Solved
Use Filter function with 2 criteria to return a specific value
3 4 5
Replies
45
Views
3K
Fluff
Fluff
J
  • Question
Remove duplicates based on multiple criteria (ID and Date)
2
Replies
17
Views
8K
johnnytominaga
J

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top