Syntax problem with CUBEMEMBER and date ranges

Greetings from Toronto Canada :wave:

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:



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:

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,

Lance

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.