Cubeset with reference to cell with a cubeset

JV0710

Active Member
Joined
Oct 26, 2006
Messages
429
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi All

I would like to ask for help with the below query:

In my Excel sheet I have some calculations using cubesets and cubevalues etc

Cubeset1
=CUBESET("my Connection","{ FILTER( { FILTER( { DESCENDANTS( [Location].[Location].[All Locations], [Location].[Location].[Store] ) }, ( [Time].[Time FinYear].["&$E$8&"], [Measures].[Sales] ) > 0 ) }, ( [Time].[Time FinYear].["&$F$8&"], [Measures].[Sales] ) > 0 ) }","Comp Stores Set")
A set of all stores where Sales in August2018 and August2019 were > 0

$E$8 = August_2018

$F$8 = August_2019

The above Cubeset works and cubesetcount = 316


I would like to edit the cubeset to not only check sales in August but April to August, so in E9 and F9 I create the following cubesets:

Cell E9 - This cubeset works and has the correct time_periods
Cubeset April_2018 to August_2018
=CUBESET("my connection","{[Time].[Time FinYear].[April_2019]:[Time].[Time FinYear].[August_2019]}","LY FYTD Set")

Cell F9 - This cubeset works and has the correct time_periods
Cubeset April_2019 to August_2019
=CUBESET("my connection","{[Time].[Time FinYear].[April_2019]:[Time].[Time FinYear].[August_2019]}","TY FYTD Set")


My new cubeset formula is now:
=CUBESET("my connection","{ FILTER( { FILTER( { DESCENDANTS( [Location].[Location].[All Locations], [Location].[Location].[Store] ) }, ( $E$9, [Measures].[TY Net Tot Sls] ) > 0 ) }, ( $F$9, [Measures].[TY Net Tot Sls] ) > 0 ) }","Comp Stores Set")

I now get “#N/A” error for the above

Please can I ask for help in getting the correct way to right this cubeset

Thanks in Advance

Joe
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Good day

For reference . . . decided to abandon the idea of cubesets for the like periods in the previous year and rather have measures created that calculate the value for the like period. That way I will only use cubeset for the locations and not need the cubeset for time.

Problem solved

Thank you
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top