Results 1 to 2 of 2

Thread: Syntax problem with CUBEMEMBER and date ranges

  1. #1
    Neophyte ranen's Avatar
    Join Date
    Jun 2019
    Excel Version

    Syntax problem with CUBEMEMBER and date ranges

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Greetings from Toronto Canada

    I have a table of tickets (problem mgmt) and I'm challenged to construct a arrival / closure / backlog chart to show how the organization is doing, at large, in keeping up with arrival rates.

    The combo chart will be 2 bars (arrival & closure) on the primary axis along with a line for backlog on secondary axis. The horizontal axis is the last 13 months of operation. Manually created, it looks like this:

    Click image for larger version. 

Name:	arrival closure backlog.jpg 
Views:	12 
Size:	64.1 KB 
ID:	9186

    I've been using cube functions successfully to correctly calculate the arrival and closure counts for each month, but I am really challenged to find the "secret" sauce syntax to support the backlog count.

    I've been trying to use date ranges within a CUBEMEMBER function call, but I keep getting '#N/A' on the following syntax:
    =CUBEMEMBER("ThisWorkbookDataModel","[ProblemTickets].[Initial Submission Date].&[2018-01-01T00:00:00]:[ProblemTickets].[Initial Submission Date].&[2018-02-01T00:00:00]","Jan Tickets")

    I've been scouring the web for solid examples of CUBE functions ... and unless I'm looking in the wrong places, the material is really (really) thin and I haven't had much success in finding good sources.

    If anyone has some handy references to CUBE functions and date range usage, I'd be very appreciative.

    And of course, if I'm missing something very basic, I'd be happy to receive some comments about how to fix my syntax above.

    Thank you in advance,

    Last edited by ranen; 2019-06-19 at 08:08 PM.

  2. #2
    Neophyte Tobriand's Avatar
    Join Date
    Jan 2019
    Excel Version

    Cube formulae

    So cube functions are horrible, and thankfully it's been a while since I used them. That said, I think there are two issues you're running into:

    1. Your CUBEMEMBER identifies a set, not a member. You may have more luck using CUBESET.

    2. If memory serves, index-style member references have pretty bad support (i.e. where you've used the &). Try using the name instead.

    Hope one of those does the job. Failing that, I'd suggest using another means of obtaining the data. CUBE formulae are ok for 20 values or so, but more than that and you'll experience pain.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts