1

I am writing a series of queries to my workbook's data model to retrieve the number of documents by Category_Name which are greater than a certain numbers of days old (e.g. >=650).

Currently this formula (entered in celll C3) returns the correct number for a single Days Old value (=3).

=CUBEVALUE("ThisWorkbookDataModel",
            "[Measures].[Count of Docs]",
            "[EDD_Report].[Category_Name].&["&$B2&"]",
            "[EDD_Report_10-01-18].[Days Old].[34]")

How do I return the number of documents for Days Old values >=650?

The worksheet looks like:

     A            B             C
 1  Date        PL        Count of Docs
 2  10/1/2018   ALD             3
 3  ...

UPDATE: As suggested in @ama 's answer below, the expression in step B did not work.

However, I created a subset of the Days Old values using 

 =CUBESET("ThisWorkbookDataModel",
          "{[EDD_Report_10-01-18].[Days Old].[all].[650]:[EDD_Report_10-01-18].[Days Old].[All].[3647]}")

The cell containing this cubeset is referenced as the third Member_expression of the original CUBEVALUE formula. The limitation is now that the values for the beginning and end must be members of the Days Old set.

This is limiting, in that, I was hoping for a more general test for >=650 and there is no way to guarantee that specific values of Days Old will be in the query.

Zephyr Mays
  • 477
  • 3
  • 7
  • 24

1 Answers1

1

First time I hear about CUBE, so you got me curious and I did some digging. Definitely not an expert, but here is what I found:

MDX language should allow you to provide value ranges in the form of {[Table].[Field].[All].[LowerBound]:[Table].[Field].[All].[UpperBound]}.

A. Get the total number of entries:

D3 =CUBEVALUE("ThisWorkbookDataModel",
           "[Measures].[Count of Docs]",
           "[EDD_Report].[Category_Name].&["&$B2&"]"),
           "{[EDD_Report_10-01-18].[Days Old].[All]")

B. Get the number of entries less than 650:

E3 =CUBEVALUE("ThisWorkbookDataModel",
           "[Measures].[Count of Docs]",
           "[EDD_Report].[Category_Name].&["&$B2&"]"),
           "{[EDD_Report_10-01-18].[Days Old].[All].[0]:[EDD_Report_10-01-18].[Days Old].[All].[649]}")

Note I found something about using .[All].[650].lag(1)} but I think for it to work properly your data might need to be sorted?

C. Substract

C3 =D3-E3

Alternatively, go for the quick and dirty:

=CUBEVALUE("ThisWorkbookDataModel",
           "[Measures].[Count of Docs]",
           "[EDD_Report].[Category_Name].&["&$B2&"]"),
           "{[EDD_Report_10-01-18].[Days Old].[All].[650]:[EDD_Report_10-01-18].[Days Old].[All].[99999]}")

Hope this helps and do let me know, I am still curious!

Ama
  • 1,373
  • 10
  • 24
  • So the expression in step B did not work. However, I created a subset of the `Days Old` values using `=CUBESET("ThisWorkbookDataModel","{[EDD_Report_10-01-18].[Days Old].[all].[650]:[EDD_Report_10-01-18].[Days Old].[All].[3647]}")` . The cell containing this cubeset is referenced as the third `Member_expression` of the formula. The limitation is now that the values for the beginning and end must be members of the `Days Old` set. This is limiting, in that, I was hoping for a more general test for `>=650` . – Zephyr Mays Sep 24 '19 at 14:34
  • Sorry just picking this up now. Are you saying that the max value must be an existing value, which is why you picked `.[All].[3647]`? Have you tried the `.Lag(1)` feature? – Ama Sep 30 '19 at 19:01
  • that is correct - 3647 is the maximum value. Will try `.Lag(1)` and report back. – Zephyr Mays Sep 30 '19 at 20:13
  • In that case why not using `=CUBESET("ThisWorkbookDataModel","{[EDD_Report_10-01-18].[Days Old].[all].[0]:[EDD_Report_10-01-18].[Days Old].[All].[649]}")` (i.e. values <650), or do you face the same problem? A walk around which I often use with pivot tables is to insert two additional items: one which would have `650 Days Old`, and one which would have `9999 Days Old`. Then you can use these two items as your boundaries, and substract 2 to the total count. – Ama Sep 30 '19 at 20:46