Forum Discussion

kalpesh07's avatar
kalpesh07
Frequent Visitor
1 year ago
Solved

Cubeset with multiple Period range?

Hey All! need help with creating a Cubeset formula! I've market data at region level for multiple period & brand! I want to know the rank of particular Brand in each region on Specific Period. Formula is working for a particular period but not working on a range! How can I use a range/dynamic period in cubeset.

=CUBESET(
    "ThisWorkbookDataModel",
    "EXISTS([Brandmaster].[Brand].children, 
    ([State_Master].[State].[Maharashtra], 
    {[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]}))",
    "Set",
    2,
    "{([Measures].[S_Value],[State_Master].[State].[Maharashtra],{[Calendar].[Month].[Oct-2024]})}"
)

This is working for particular month but when I want to get data for 3 month like Oct-Dec

 

=CUBESET(
    "ThisWorkbookDataModel",
    "EXISTS([Brandmaster].[Brand].children, 
    ([State_Master].[State].[Maharashtra], 
    {[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]}))",
    "Set",
    2,
    "{([Measures].[S_Value],[State_Master].[State].[Maharashtra],
{[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[dec-2024]})}"
)

 

I'm getting error on this. Range is working on set expression but while using same in Sort By (Highlighted Part), I'm getting the error.

One way around is passing Each Month separately not tried that but I want it to be dynamic using timeline & using Min/Max function to define the range, it could be 2month or 12 month.

Pls help me with this.

 

Thanks

  • kalpesh07 , Try using

    =CUBESET(
    "ThisWorkbookDataModel",
    "EXISTS([Brandmaster].[Brand].children,
    ([State_Master].[State].[Maharashtra],
    {[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]}))",
    "Set",
    2,
    "AGGREGATE({([Measures].[S_Value],[State_Master].[State].[Maharashtra],{[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]})}, SUM)"
    )

6 Replies

  • kalpesh07 , Try using

    =CUBESET(
    "ThisWorkbookDataModel",
    "EXISTS([Brandmaster].[Brand].children,
    ([State_Master].[State].[Maharashtra],
    {[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]}))",
    "Set",
    2,
    "AGGREGATE({([Measures].[S_Value],[State_Master].[State].[Maharashtra],{[Calendar].[Month].[Oct-2024]:[Calendar].[Month].[Dec-2024]})}, SUM)"
    )

    • kalpesh07's avatar
      kalpesh07
      Frequent Visitor

      Getting #N/A error. It is working fine till part one of the formula but after using Aggregate it is showing the error.

  • v-menakakota's avatar
    v-menakakota
    Community Support

    Hi kalpesh07 ,

    Thank you for helpful response bhanu_gautam ,I wanted to check if you had the opportunity to review the information provided by bhanu_gautam 
    . Please feel free to contact us if you have any further questions. If this answers your question, please Accept it as a solution so other members can easily find it.

    Regards,
    Menaka.

  • v-menakakota's avatar
    v-menakakota
    Community Support

    Hi kalpesh07 ,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

    • v-menakakota's avatar
      v-menakakota
      Community Support

      Hi kalpesh07 ,

      I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

      Thank you.