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

[Andy Somnifac]

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:

[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?

 

[Tupe77]

Do you mean something like this?

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."

 

[Andy Somnifac]

@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,

 

[Andy Somnifac]

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.

 

[RoryA]

To get a set of the dates, you'd want something like:

=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

 

[Andy Somnifac]

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?

 

[Andy Somnifac]

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.

 

[RoryA]

Oh - try this:

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

 

[Andy Somnifac]

Oh - try this:

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

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.