Topic Thread

Expand all | Collapse all

MDX function for Last 13 periods Growth % on Turnover

  • 1.  MDX function for Last 13 periods Growth % on Turnover

    Posted Fri October 02, 2020 12:27 PM

    Version: IBM Cognos Analytics 11.0.12

    Report Layout: Crosstab

    Report Data Source: OLAP Cube .mdc

    Requirement:
    requirement

      Current development in place:

     

    Calendar Dimension: Year, Month, Week, Day Levels

    Have a custom relative time category:

    N-Period Running Totals - 13

    Target Period – Months with 0 offset

    Context Period – Year with 0 offset

     

    Worthwhile to mention that my custom time category does not have levels present or Hierarchy at all, only members?

     

    Option 1 – Using the Calendar Dimension

     

    Works well however I am not able to limit the number of periods that are on the report to only 13 required. Effectively need 24 months to achieve the calcs, but only display last 13:

     

    tuple([Turnover Amount],parallelPeriod([Monthly Merchant Snapshot Cube].[Trading Calendar Date].[Trading Calendar Date].[Month],12))

      

    Option 2 – Using the custom relative time category

     

    This returns errors when swopping out for the custom relative time category, due to no levels present.

     

    Is there a way to generate the Growth in Transformer for each of the Last 13 periods (by Period) or should this be handled in Report Studio via MDX.

    Any assistance would be greatly appreciated.

    Many Thanks

     



    ------------------------------
    Carl van Zyl
    ------------------------------


  • 2.  RE: MDX function for Last 13 periods Growth % on Turnover

    Posted Fri October 02, 2020 12:29 PM
    Carl, thanks for reaching out! I would consider asking this question in our Cognos Analytics Community.

    ------------------------------
    Isaiah Brown
    ------------------------------