0

I have the below CUBESET function that generates SET of LOCATION COUNTRIES (names) ordered by Quantity, which works fine. I would need the same but using multiple months. Each month that I add in the formula below is multiplying the list of members I receive. So if I add another month then I receive a list of countries as "USA,USA,GER,GER..." .I would need to receive it only once. There should be a calculation that would first sum up the Quantity for all months and then it would order all the months based on the Total Quantity per Country.

=CUBESET("Connection_to_OLAP";" 
         (
           [Customer].[Customer No].[All].[100]
          ,[Date].[Calendar Year].[All].[2017]
          ,[Date].[Calendar Month Of Year No].[All].[01]
         ,{[Location].[Location Country Code].children})";"Country";1;"([Measures].[Quantity])")
whytheq
  • 34,466
  • 65
  • 172
  • 267
Jar
  • 13
  • 4

1 Answers1

0

If you create a custom member that is something like:

Aggregate(
  [Date].[Calendar Year].[All].[2017] 
  * {[Date].[Calendar Month Of Year No].[All].[01], [Date].[Calendar Month Of Year No].[All].[02]}
)

The above could be called "TwoMonthsAgg".

Then your CUBESET would change to:

=CUBESET("Connection_to_OLAP";" 
         (
           [Customer].[Customer No].[All].[100]
          ,[Date].[All].[TwoMonthsAgg]
         ,{[Location].[Location Country Code].children})";"Country";1;"([Measures].[Quantity])")
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • I'm not quite sure where to create the custom member. I tried it as below but its not working: =CUBEMEMBER("Connection_to_OLAP";"AGGREGATE([Date].[Calendar Year].[All].[2016]*{[Date].[Calendar Month Of Year No].[All].[01],[Date].[Calendar Month Of Year No].[All].[02]})") – Jar Apr 28 '17 at 11:28