Excel Cube functions.

Question

HI

I have this function after I converted a Pivot Table to formulas

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Total Stems 2]",{"[Stem_Sales 10].[Year].&[2017]","[Stem_Sales 10].[Week].&[10]","[Stem_Sales 10].[Farm].&[Calenick]","[Stem_Sales 10].[Product DescriptionFF].&[Ayoba® Arctic Ice]"},"[Stem_Sales 10].[stem lengthcm].&[60cm]")

I want to replace the 2017 (&[2017]") to the value in A1.

I have tried a number of concatenate versions, but Excel either says there is a problem with the formula or #NA.

Thank you

Answer 1

Dear Rory

Thank you for all the help.

This one worked in the end.

=CUBEVALUE("ThisWorkbookDataModel",

CUBEMEMBER("ThisWorkbookDataModel","[Stem_Sales 8].[Year].&["&$A$6&"]"),

CUBEMEMBER("ThisWorkbookDataModel","[Stem_Sales 8].[Farm].&["&$A$3&"]"),

CUBEMEMBER("ThisWorkbookDataModel","[Stem_Sales 8].[Product DescriptionFF].&["&$A$4&"]"),

CUBEMEMBER("ThisWorkbookDataModel","[Stem_Sales 8].[stem lengthcm].&["&$A$5&"]"),

CUBEMEMBER("ThisWorkbookDataModel","[Stem_Sales 8].[Week].&["&B8&"]"),

CUBEMEMBER("ThisWorkbookDataModel","[Stem_Sales 8].[Sales Category].&["&$A$2&"]"),

"[Measures].[Sum of Total Stems]")

I separated each cubememember, the {} seems to be the problem.

Regards

Es

Answer 2

Dear Rory

Thank you for the reply.

I have tried that version, but the error message "there is a problem with this formula" pops up and if I press ok for this message it highlights [" that is in front of ["&A1&"].

I typed 2017 in A1

Answer 3

Hi,

It should just be:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Total Stems 2]",{"[Stem_Sales 10].[Year].&["&A1&"]","[Stem_Sales 10].[Week].&[10]","[Stem_Sales 10].[Farm].&[Calenick]","[Stem_Sales 10].[Product DescriptionFF].&[Ayoba® Arctic Ice]"},"[Stem_Sales 10].[stem lengthcm].&[60cm]")