Cubeset with reference to cell with a cubeset

JV0710

Active Member
Joined
Oct 26, 2006
Messages
351
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
 

JV0710

Active Member
Joined
Oct 26, 2006
Messages
351
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
 

Forum statistics

Threads
1,085,010
Messages
5,381,209
Members
401,718
Latest member
Ereese20

Some videos you may like

This Week's Hot Topics

Top